Database Query Using–SQL Computer Science - Database Query Using–SQL

What will be the degree and cardinality of these tables in the cartesian product?

Table: Product

(a) Write the names of most appropriate columns, which can be considered as candidate keys.

(b) What is the degree and cardinality of the above table?

(UPDATE, SELECT, ALTER, DROP)

Table: DEPT

Table: WORKER

Note: DOJ refers to date of joining and DOB refers to date of Birth of workers.

(i) To display Wno, Name, Gender from the table WORKER in descending order of Wno.

(ii) To display the Name of all the FEMALE workers from the table WORKER.

(iii) To display the Wno and Name of those workers from the table WORKER who are born between ‘1987-01-01’ and ‘1991-12-01’.

(iv) To count and display MALE workers who have joined after ‘1986-01-01’.

(v) SELECT COUNT(*), DCODE FROM WORKER GROUP BY DCODE HAVING COUNT(*)>1;

(vi) SELECT DISTINCT DEPARTMENT FROM DEPT;

(vii) SELECT NAME, DEPARTMENT, CITY FROM WORKER W,DEPT D WHERE W.DCODE=D.DCODE AND WNO<1003;

(viii) SELECT MAX(DOJ), MIN(DOB) FROM WORKER;

Table: STORE

Table: ITEM

(a) Write the SQL queries (1 to 4):

(i) To display IName and Price of all the items in the ascending order of their Price.

(ii) To display the SNo and SName or all stores located in CP.

(iii) To display the minimum and maximum price of each IName from the table Item.

(iv) To display the IName, price of all items and their respective SName where they are available.

(b) Write the output of the following SQL commands (i) to (iv):

Table: CARHUB

Table: CUSTOMER

(a) Write SQL commands for the following statements:

(i) To display the names of all white colored vehicles

(ii) To display name of vehicle, make and capacity of vehicles in ascending order of their sitting capacity

(iii) To display the highest charges at which a vehicle can be hired from CARHUB.

(iv) To display the customer name and the corresponding name of the vehicle hired by them.

(b) Give the output of the following SQL queries:

(i) Select count(distinct make) from cabhub;

(ii) Select max(charges), min(charges) from carhub;

(iii) Select count(*), make from carhub;

(iv) Select vehiclename from carhub where capacity = 4;

[Delhi 2011]

Relation: WORKER

Relation: PAYLEVEL

(a) Write SQL commands for the following statements:

(i) To display the details of all WORKERs in descending order of DOB.

(ii) To display NAME and DESIG of those WORKERs whose PLEVEL is either P001 or P002.

(iii) To display the content of all the WORKERs table, whose DOB is in between ’19-JAN-1984’ and ’18-JAN-1987’.

(iv) To add a new row with the following:

19, ‘Daya kishore’, ‘Operator’, ‘P003’, ’19-Jun-2008’, ’11-Jul-1984’

(b) Give the output of the following SQL queries:

Write SQL queries for (a) to (d) and find outputs for SQL queries (e) to (h), which are based on the tables. [CBSE Sample Paper 2017]

TABLE: WATCHES

TABLE: SALE

(a) To display all the details of those watches whose name ends with ‘time’

(b) To display watch’s name and price of those watches which have price range between 5000-15000

(c) To display total quantity in store of unisex type watches

(d) To display watch name and their quantity sold in first quarter

(e) Select max(price),min(qty_store) from watches;

(f) Select quarter, sum(qty_sold) from sale group by quarter;

(g) Select watch_name,price,type from watches w, sale s where w.watchid=s.watchid;

(h) Select watch_name, qty_store, sum(qty_sold),qty_store

Write the SQL query questions from (i) to (iv) and write the output of SQL command for questions from (v) to (vii) given below:

Table: EMPSalary

Write the SQL commands for the following :

(i) To show firstname, lastname, address and city of all employees living in paris.

(ii) To display the content of Employees table in ascending order of Firstname.

(iii) To display the firstname,lastname and total salary of all managers from the tables employee and empsalary, where total salary is calculated as
salary+benefits.

(iv) To display the maximum salary among managers and clerks from the table Empsalary.

Give the Output of following SQL commands:

(v) Select firstname, salary from employees, empsalary where designation = ‘Salesman’ and Employees.

empid=Empsalary.empid;

(vi) Select count(distinct designation) from empsalary;

(vii) Select designation, sum(salary) from empsalary group by designation having count(*) >2;

Table: Bank

Write SQL commands for the statements (a) to (h) on the table Bank

(a) Display the details of all FD whose rate of interest is in the range 6% to 7%.

(b) Display the Customer Name and FD Amount for all the loans for which the number of Months is 24, 36, or 48(using IN operator).

(c) Display the Account Number, Customer Name and FD Amount for all the FD for which the Customer Name ends with “Sharma”.

(d) Delete the records of “Rupinder Kaur”.

(e) Add another column Maturity_Amt of type Integer in the Bank table.

(f) To find the average FD amount. Label the column as “Average FD Amount”.

(g) To find the total FD amount which started in the year 2018?

(h) Update Maturity Amount of all bank customers.

a. Maturity Amount = (FD_Amount*Months* Int_rate)/(12*100)

Table: Bank

Write SQL commands for the statements (a) to (g) on the table BANK

(a) Display details of all the FD whose rate of interest is NOT NULL.

(b) Display amounts of various FD from the table Bank. An FD Amount should appear only once.

(c) Display the number of months of various loans from the table Bank. A month should appear only once.

(d) Display the Customer Name and FD Amount for all the Bank which do not have a number of months is 36.

(e) Display the Customer Name and FD Amount for which the FD amount is less than 500000 or int_rate is more than 7.

(f) Display the details of all FD which started in the year 2018.

(g) Display the details of all FD whose FD_Amount is in the range 40000 to 50000.

Table: Bank

Write SQL commands for the statements (a) to (g) on the table BANK

(a) To create the table Bank (Primary Key: AccNo)

(b) Display the structure of the table Bank.

(c) Display the details of all the bank.

(d) Display the AccNo, Cust_Name, and FD_Amount.

(e) Display the details of all the FD’s having maturity time is less than 40 months.

(f) Display the AccNo and FD amount which started before 01-04-2018.

(g) Display details of all FD whose rate of interest are NULL.

(a) To insert a new row in the HOSPITAL table with the following data: 11,’ Kasif’, 37,’ENT’,’2018-02-25’, 300, ’M’.

(b) To set charges to NULL for all the patients in the Surgery department.

(c) To display patient details who are giving charges in the range 300 and 400 (both inclusive).

(d) To display the details of that patient whose name second character contains ‘a’.

(e) To display total charges of ENT Department.

(f) To display details of the patients who admitted in the year 2019.

(g) To display the structure of the table hospital.

(h) Write the command to create the above table.