0% found this document useful (0 votes)
116 views5 pagesDBMS Final Paper
Uploaded by
Usama Zia ButtCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
0% found this document useful (0 votes)
116 views5 pagesDBMS Final Paper
Uploaded by
Usama Zia ButtCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
/ 5
Nec UNIVERSITY OF GUJRAT
Final Term Examination, Fall 2020
rn Tle reeton ta Seon
Database Systems
Time: 3.5 Hours Marks: 50
Course Code: SE-202, IT-243
ROLL NO:
1. Students are advised to write complete information on the front page, Name, Roll no.,
Section, Subject, Exam Date, Instructor Name.
2. Students are advised to use white pages (with line or blank) in Final Term Examination.
3. Final Term Question paper will be received through Instructor’s Email address/UOG-LMS/MS
Teams/Whatsapp (optional) as mentioned date and time in Final Date sheet.
4. Final Term Exam time is included in two parts as Part-1 is for paper attempt time (2 Hours
and 30 Minutes), Part-2 is for paper Downloading and uploading time (1 hour). Total time
given is 3 hours 30 Minutes
5. Students shall submit solved handwritten paper in POF format (non-editable) to the
Instructor concerned by the notified stimulated time
6. Solved Paper after the due date and time shall not be considered
Q.No Marks
1 Normalize the database of your term project to third normal form. Steps of 5
normalization should be clear and complete, as these have definite marks.
2 Draw Entity Relationship Diagram for following scenario by adopting necessary steps 5
using crow foot notations.
A salesperson may manage many other salespeople. A salesperson may not manage other
salespeople. A salesperson is managed by one and only one salespeople. A salesperson
can be an agent for at least one customers. A customer is managed by one salespeople.
A customer can place many orders. A customer may not place any order. An order can be
placed by one customer. An order lists many inventory items. An inventory item may be
listed on many orders. An inventory item is assembled from at least a part. A part may
be assembled into many inventory items. A part may be assembled into one inventory
items. A supplier may supplies many parts. A supplier may not supplies any part. A part
may not be supplied by any suppliers. A part may be supplied by many suppliers
3 Write SQL(oracle) statements to create tables (first three tables) of third normal form of 6
question no. 1.
4 Write SQL statements for following, consider following Branch and Staff tables 1x15
215
Table Branch
DEPARTMENT ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION 1D
10 | Administration (e200) 1700
i I 1
I 20 | Marketing [e201 le Asoo
I 30 | Purchasing | ana | 1700}
i 40 | Human Resources | 203 | 2400 |
I 50 | Shipping | 42a 1 1500 |
1 60 iar | 103 | 1400 |
I 70 | Public Relations | 204 | 2700}
I 80 | sales | 145 | 2500 |
I 90 | Executive [| 200) = i700)
i 100 | Finance | 108 | 1700 1
Table Staff
EMPLOYEE_ID | NAME | HIRE DATE | J08_1D
SALARY [COMM] MANAGER ID | DEPARTMENT 1D]
| 100 | Steven | 2003-06-17 | AD_PRES | 24000.00| 0.00 | o| 901
| 101 | Nena | 2005-09-21 | aD_vP | 1700.00) 0.00 | 100 | 90 |
| 102 | tex | 2001-01-13 | AD_vP | 1700.00) 1 100 | 90 |
| 103 | Alexander | 2006-01-03 | IT PROG | 9000.00 100 | 102 | 60 |
| 108 | Bruce | 2007-05-21 | IT PROG | 6000.00 | 0.00 | 103 | 60 |
| 105 | David | 2005-06-25 | IT PROG | 4800.00 0.00 | 103 | 60 |
| 106 | valli | 2006-02-05 | T_PROG | 4800.00 | 1 103 | 60 |
| 107 | Diana | 2007-02-07 | IT PROG | 4200.00 | 0.00 | 103 | 60 |
| 108 | Naney | 2002-08-17 | FILMGR | 1208.00] 200 | 1011 100 |
| 109 | Danie! | 2002-08-16 | FI_ACCOUNT | 9000.00 500 | 108 | 300 |
| 110 | John | 2005-09-28 | FI_ACCOUNT | 8200.00 | 0.00 | 108 | 100 |4.1-Write a query in SQL to display the name, department number and department
name, for all employees for departments 80 or department of manager whose id is 200.
4.2-Write 2 query in SQL to display those employees who contain a letter z to their name
and also display their department and their manager name
4.3-Write a query in SQL to find the employee id, name, hiredate of those employees
who are earning more than average salaries of executive department and getting no
commission.
4.4-Write a query in SQL to find salary of all employees whose first letter of name is D
other letters are unknown total latter are five and their respective department exists at
1700 location id. Show the results in increasing order of salaries.
4,5- Write a query in SQL to find the name, department name arranged the result
according to the department name appear alphabetically in ascending order. Show the
result of only those employees who are earning less than or equal to average salaries of
IT department
4.6- Write a query in SQL to display name, job id, hiredte, department name for those
employees with worked without a commission percentage with their location id is not
1700 and manager id is not more than 150 and not less than 100. Show the result in
Increasing order of hiredate and decreasing order of employee name
4.7- Write a query to display the employee id, name, department name and job id for all
employees whose salary is smaller than any salary of those employees whose job title is
FI_LMGR. Exclude Job title FI_MGR
4.8- Write a query to display the employee id, name, department name and the job id
column with a modified column title MAN for those employees who are earning salaries
more than average comm if given to all employees.
4,9- Write @ query to display the employee id, name, Salary, annual salary and the
dailySalary for those employees whose salary is more than and less than the average
salary of all employees having department name starts with latter P.
4.10- Write 2 query which is looking for the names, department name of all employees
whose salary is greater than 50% of their department’s total salary bill of those
employees who are earning commission more than 123.
4.11: Write a query to display the name, salary, department name for all the employees
who gets the salary as the salary earn by the employee which is minimum within the
joining person June 1st, 2002 and December 31st, 2003. Show the result in a statement
like “Mr. Lex is getting 17000 and working in Executive department”
4.12- Write a query in SQL to display the name, department name, separated by hyphen,
and salary, for those employees whose salary is within the range of 9000 and 17000 for
those employees whose department name is ending with the letter € and starting with E
keep in mind that department name may in in any case.
4,13- Write a query in SQL to display the name, salary, and department name for those
employees who earn less than the average salary, and also work at the department
where the employee Laura is working. Show your result as like “David is getting 4800 in
IT department”
4,14- Write a query in SQL to display the department name, name, salary for those
employees who is highest salary drawar in a department. Exclude the department whose
name is starting with letter A.
4.15: Write a query in SQL to display the name of manager who is supervising 4 or more
employees and his department name contain latter R in between.
Write SQL statements for following, consider following seller, purchaser and order table. 1X5
Statements must be executable and error free.
Table sellersalesman_id | name | city | commission
5001 | James Hoog | New York | 0.15
5002 | Nail Knite | Paris | 0.13
5005 | Pit Alex | London | eat
5006 | Mc Lyon | Paris | e.1a
5007 | Paul Adam | Rome — | 0.13
5003 | Lauson Hen | San Jose | 0.12
Table purchaser
customer_id | cust_name | city | grade | salesman_id
3002 | Nick Rimando | New York | 100 | 5001
3007 | Brad Davis | New York | 200 | 5001
3005 | Graham Zusi | California | 200 | 5002
3008 | Julian Green | London | 300 | 5002
3004 | Fabian Johnson | Paris | 300 | 5006
3009 | Geoff cameron | Berlin | 100 | 5003
3003 | Jozy Altidor | Moscow | _200 | 5007
Table Order
ordno —purch_amt ord.date customer_idsallesman_id
70001 150.5, 2012-10-05 3005, 5002
70003 270.65 2012-09-18 3001 5005
70002 65.26 2012-10-05 3002 5001
70008 10.5 2012-08-17 3009 5003
70007 948.5 2012-09-10 3005, 5002
70005 2400.6 2812-07-27 3007 5001
70008 5760 2012-09-10 3002 5001
5.1- Write a SQL statement to display all those orders by the customers located in the
same cities where their salesmen live. Exclude the salesman earning no commission and
belongs to city ending with s. keep in mind that we are not sure about later case of city
name.
5.2- Write @ SQL statement that shorts out the customer, their grade against order no.
Each of the customers must have a grade and who belongs to a city. Show your result
without any space.
5.3- Write a query that produces all those customers with their name, city, salesman and
commission, who served by a salesman and the salesman works at a rate of the
commission within 12% to 14%. Show result city name increasing and commission
decreasing. Exclude the result of those salesman who belong to a city with second last
word is i.
5.4- Write a SQL statement that produces all orders with the order number, customer
fame, commission rate and earned commission amount for those customers who carry
their grade is 200 or more and served by an existing salesman. Don’t display order no
which were ordered before 2012.
5.5- Write a query to display all customers with orders on October 5, 2012 and belongs
to the city of salesman with id 5001.
Find and fix the error in the syntax by correcting the syntax. Statements must be executable and error 1X4
free =4
61
SELECT order.ord_no,
order.purch_amt,
customer.cust_name,
customer.city
FROM orders, customer
WHERE order.customer_id=customer.customer_id
Group by order.orderno
AND order.purch_amt BETWEEN 500 AND 2000
OR customer.city is null;
62
select distinct sal, comm, deptno, location
from emp et
where 3 = (select count(distinct sal)
from emp e2 where e1.sal
