Showing posts with label SQL Exercise. Show all posts
Showing posts with label SQL Exercise. Show all posts

Sunday, 7 October 2018

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / Solved relational algebra queries

LIST OF TABLES:
VISITORS (Visitor Id, Name, Age, Gender, Address, Contact_no, Email, Entry_time, Exit_time, Date_of_visit, Mode_of_Transport)
EVENTS (Event_ID, Event_Name, Capacity, Timings, Category)
EMPLOYEE (Emp_ID, Emp_Name, Address, Contact_No, Gender, Department, Shift, Salary, DOB, Join_Date, Event_Id)
MAINTENANCE_PROBLEMS (Complaint_Id, Complaint_Name, Date_of_complaint, Status, Date_of_completion, Employee_Id, Event_Id)
BOOKINGS (Ticket_No, Payment_Mode, Card_type, Price_Before_discount, Price_After_Discount, No_of_visitors, Visitor_Id, Event_Id)

SQL and Relational Algebra queries:

1) Give the name of the visitors whose mode of transport to amusement park is car?
What is to be found? [columns]
Name
Where to search? [tables]
Visitors
What are search conditions?
Mode_of_transport = ‘car’

SQL: SELECT Name FROM Visitors WHERE Mode_of_transport = ’car’;
RA: Π NAMEMODE_OF_TRANSPORT = ’CAR’ (VISITORS))

2) Give the name and address of the visitors who visited the amusement park on 2-10-2018?
What is to be found? [columns]
Name, Address
Where to search? [tables]
Visitors
What are search conditions?
Date_of_visit = ’01-OCT-2018’

SQL: SELECT Name, Address FROM Visitors WHERE Date_of_visit = ’01-OCT-2018’;
RA: Π NAME, ADDRESS DATE_OF_VISIT = ‘01-OCT-2018’ (VISITORS))

3) Give the Name of the events which has the accommodation for more than 200 visitors?
What is to be found? [columns]
Event_name
Where to search? [tables]
Events
What are search conditions?
Capacity > 200

SQL: SELECT Event_name From Events WHERE Capacity > 200;
RA: Π EVENT_NAME( σ CAPACITY > 200 (EVENTS))

4) What is the time of event E150?
What is to be found? [columns]
Timings
Where to search? [tables]
Events
What are search conditions?
Event_ID = ‘E150’

SQL: SELECT Timings From Events WHERE event_ID = ‘E150’;
RA: Π TIMINGS( σ EVENT_ID = 150 (EVENTS))

5) List the details of male employees who work during night shift?
What is to be found? [columns]
All columns
Where to search? [tables]
Employee
What are search conditions?
Gender = ‘Male’ AND Shift = ‘night’

SQL: SELECT * FROM Employee WHERE Gender = ‘Male’ AND Shift = ‘night’;
RA:GENDER = ’MALE’ ^ SHIFT = ’NIGHT’ (EMPLOYEE))

6) Give the contact number of the employees whose salary is more than 30000 and less than 45000?
What is to be found? [columns]
Contact_no
Where to search? [tables]
Employee
What are search conditions?
Salary > 30000 and Salary < 45000

SQL: SELECT Contact_no FROM Employee WHERE Salary > 30000 AND salary < 45000;
(or)
SELECT Contact_no FROM Employee WHERE Salary BETWEEN 30000 AND 45000;
RA: Π CONTACT_NOSALARY > 30000 ^ SALARY < 45000 (EMPLOYEE))

7) List down the details of complaints filed on 2-10-2018?
What is to be found? [columns]
All columns
Where to search? [tables]
Maintenance_Problems
What are search conditions?
Date_of_complaint = ’01-OCT-2018’

SQL: SELECT * FROM Maintanence_Problems WHERE Date_of_Complaint = ‘02-OCT-2018’;
RA:DATE_OF_COMPLAINT = ‘02-0CT-2018’ (MAINTANENCE_PROBLEMS))

8) Give the complaint ids of the complaints that are completed on ’02-10-2018’ and ’03-10-2018’?
What is to be found? [columns]
Complaint_id
Where to search? [tables]
Maintenance_Problems
What are search conditions?
(Date_of_Complaint = ‘02-OCT-2018’ OR Date_of_Complaint = ‘03-OCT-2018’) AND status = ‘Completed’

SQL: SELECT complaint_id FROM Maintanence_Problems WHERE Date_of_Complaint = ‘02-OCT-2018’ OR Date_of_Complaint = ‘03-OCT-2018’ AND status = ‘Completed’;
RA:DATE_OF_COMPLAINT = ‘02-0CT-2018’ V DATE_OF_COMPLAINT = ‘02-0CT-2018’ ^ STATUS = ’Completed’ (MAINTANENCE_PROBLEMS))

9) Give the Ticket Nos and the number of visitors for each ticket whose payment mode is net-banking?
What is to be found? [columns]
Ticket_No, No_of_visitors
Where to search? [tables]
Bookings
What are search conditions?
Payment_Mode = ’Net Banking’

SQL: SELECT Ticket_No, No_of_visitors FROM Bookings WHERE Payment_Mode = ’Net Banking’;
RA: Π TICKET_NO, NO_OF_VISITORSPAYMENT MODE=’NET BANKING’ (BOOKINGS))

10) Find the price after discount for all tickets for which the number of visitors are more than 5 and the card types are ‘VISA’ and ‘MasterCard’?
What is to be found? [columns]
Price_After_Discoun
Where to search? [tables]
Bookings
What are search conditions?
No_of_visitors > 5 AND (Card_type = ‘VISA’ OR Card_type = ‘Mastercard’)

SQL: SELECT Price_After_Discount FROM Bookings WHERE No_of_visitors > 5 AND Card_type = ‘VISA’ OR Card_type = ‘Mastercard’;
RA: Π PRICE_AFTER_DISCOUNTNo_of_visitors > 5 ^ Card_type = ‘VISA’ V Card_type = ‘Mastercard’ (BOOKINGS))

*************







Monday, 23 July 2018

Table creation in SQL solved exercise 1


Question:
Write SQL queries to create tables for company database as per the specification given below; [Note: Primary keys are underlined. If not, identify one.]

EMP(SSN, Name, Gender, Birthdate, Street, City, DNO, SuperSSN, Salary) – stores information about employees, with SSN as key. The DNO denotes the department to which they are assigned and the SuperSSN is the SSN of their supervisor and supervisor is one of the employees. Every employee has a supervisor (assume the CEO has a supervisor with a fictitious SSN). Gender can be one of either ‘M’ or ‘F’.

DEPT(DeptNo, DeptName, MgrSSN) – stores information about the departments such as the unique department number, a unique department name (Dname), and the SSN of the department manager (every department must have a manager).

PROJECTS(PNum,Pname, PLocation, PCost, DNO) – information about projects at the company, with unique Project number (PNO). The project has a name and a location and is controlled/managed by a department. PCost is the total cost for a project given that it should be in the range of 100000 to 1 crore.

WORKS(ESSN, PNO, Hours) – information about the projects that each employee works on and the number of hours they work on each project in a day. The number of hours cannot exceed 8 hours. An employee may work on more than one project.

Queries:
Notes:

  • To create all these tables you must follow certain order. The attributes of one table refers other tables (foreign key). We may consider the referring table as child and the referred table as parent. If you need to refer, then the parent table should exist. Hence, the following tables created without foreign key attributes and later we may use ALTER TABLE statement to add the foreign keys.
  • The datatype and size of a foreign key attribute should be same as the referred attribute. For example, for the first table EMP the datatype and size of DNO attribute should be same as DNUM attribute of DEPT table.
 

Queries
Description
CREATE TABLE EMP (
SSN Number(5) PRIMARY KEY,
Name Varchar(50),
Gender Char(1),
Birthdate Date,
Street Varchar(50),
City Varchar(50),
DNO Number(3),
SuperSSN Number(5),
Salary Number(9,2),
CHECK (Gender IN (‘M’, ‘F’)),
FOREIGN KEY (DNO) REFERENCES DEPT(DNUM),
FOREIGN KEY (SuperSSN) REFERENCES EMP(SSN));
SSN is the key.

Gender can have only two values ‘M’ or ‘F’.

DNO refers DNUM attribute of DEPT table.

SuperSSN refers SSN of same (EMP, Self reference) table, because supervisor is one of the employees.
CREATE TABLE DEPT (
DNUM Number(3) PRIMARY KEY,
DNAME Varchar(50) UNIQUE,
MGRSSN Number(5),
FOREIGN KEY (MGRSSN) REFERENCES EMP(SSN));
DNUM is the key attribute.

DNAME is not a key but should have unique values, ie., no duplicate values are permitted.

MGRSSN is the manager SSN refers SSN attribute of EMP table.
CREATE TABLE PROJECTS(
PNum Number(5) PRIMARY KEY,
Pname Varchar(50),
Plocation Varchar(50),
PCost Number(10,2)
DNO Number(3),
CHECK (PCost >= 100000 AND PCost <= 10000000),
FOREIGN KEY (DNO) REFERENCES DEPT(DNUM));
PNum is the key attribute.

Project cost should be in the range of 1 lakh to 1 crore.

DNO refers DNUM attribute of Dept table.
CREATE TABLE WORKS(
ESSN Number(5),
PNO Number(3),
HOURS Number(1),
PRIMARY KEY (ESSN,PNO),
CHECK (HOURS BETWEEN 1 AND 8),
FOREIGN KEY (ESSN) REFERENCES EMP(SSN));
FOREIGN KEY (PNO) REFERENCES PROJECTS(PNUM));
The combination of ESSN and PNO form the key (called table level primary key).

Number of hours should not exceed 8.

ESSN (Employee SSN) refers SSN attribute of EMP table.

PNO refers PNUM attribute of PROJECTS table.

***********

 






How to create tables using SQL?
Table creation with Foreign keys,
How to use CHECK constraint in Oracle SQL,
Self referencing tables in Oracle,
How to create tables with multiple foreign keys in Oracle?
 




SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...