VELAMMAL BODHI CAMPUS PONNERI
Topic: Database &SQL [Worksheet]
COMPUTER SCIENCE[083]
[Link] the SQL commands for the questions (i) to (iv) based on the
relations MOBILEMASTER & MOBILESTOCK given below:
Table: MOBILEMASTER
Write the SQL commands for the questions (i) to (iv) based on the
relations MOBILEMASTER & MOBILESTOCK given below:
Table: MOBILEMASTER
i) Display the total Quantity of mobiles and mobile name available for
every mobile.
ii) Display the name of the Suppliers supplying the mobile of the
company “Nokia”
iii) Decrease the price of the mobile by 2000 whose manufacturer date
is between 2011 and 2013.
iv ) A) Display the total cost of the “Micromax” company mobiles.
(total cost is price *quantity)
OR
B) Add a new column called discount of type integer to the
MOBILEMASTER
[Link] the table Employee and answer the questions under the
options A or B given below:
Table: Employee
Note: The table contains many more records than shown here.
A) Write SQL Queries for the following questions:
i) Display the total salary of all employees who had assigned a
project.
ii) Display the number of employees assigned for each project.
iii) Display the project details without repetition.
iv) Display the employee name who born after October 1992.
OR
B) Write the output of the queries (i) to (iv) based on the table
Employee:
i) select NAME, PROJECT from Employee order by PROJECT;
ii) select NAME, SALARY from Employee where DOJ like '2015%';
iii) select NAME, DOB, DOJ from Employee where SALARY
between 100000 and
200000;
iv) select min(DOJ), max(DOB) from employee;
[Link] organization SoftSolutions is maintaining their employee’s
records using SQL table.
Table: HRDATA
Ecode EName Desig Remn
80001 Lokesh Programmer 50000
80004 Aradhana Manager 65000
80007 Jeevan Programmer 45000
80008 Arjun Admin 55000
80012 Priya Executive 35000
i) a) Identify the candidate keys and primary key of the above table.
b) What constraint can be applied to EName so that no NULL values
are permitted.
ii) a) Add a column REMARKS in the table with datatype as varchar
and size as 100 characters
b) Delete the column Remn from HRDATA.
[Link] the SQL Queries for (i) to (iv) based on the table
EMPLOYEE and DEPARTMENT.
Table – EMPLOYEE
i) To display department name and number of employees in each
department.
ii) To display department name and sum of the salary spent by the
department, where
the total amount spent by the department as salary is more than
1,00,000.
iii) To display the name of the employee in descending order of their
seniority
iv) A) Write a command to modify the DEPT column of EMPLOYEE
table, so that the column can accept values only when such DEPT_ID
is available in the DEPARTMENT table.
OR
B) Add a new column called DOB of type date to EMPLOYEE.
5. Consider the table Employee and answer the questions under the
options A or B given below:
A) Write SQL Queries for the following questions:
i) Display the total salary of all employees who had assigned a
project.
ii) Display the number of employees assigned for each project.
iii) Display the project details without repetition.
iv) Display the employee name who born after October 1992.
OR
B) Write the output of the queries (i) to (iv) based on the table
Employee:
i) select NAME, PROJECT from Employee order by PROJECT;
ii) select NAME, SALARY from Employee where DOJ like '2015%';
iii) select NAME, DOB, DOJ from Employee where SALARY
between 100000 and 200000;
iv) select min(DOJ), max(DOB) from employee;
6. A CD/DVD Shop named “NEW DIGITAL SHOP” stores various
CDs & DVDs of songs/albums/movies and use SQL to maintain its
records using SQL table.
i) a) Write the Degree & Cardinality of the relation LIBRARY. Also
Identify the best attribute which may be declared as Primary key.
OR
b) What constraint can be applied to Name so that no NULL values
are permitted.
ii) a) Insert the following record in the above relation: (10009,
”Motivational Songs”, 15, 70)