company database n.
Download
Skip this Video
Download Presentation
Company Database

Loading in 2 Seconds...

play fullscreen
1 / 29

Company Database - PowerPoint PPT Presentation


  • 242 Views
  • Uploaded on

Company Database. CREATE TABLE DEPARMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SNN) ).

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Company Database' - iolana


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2

CREATE TABLE DEPARMENT (

DNAME VARCHAR(10) NOT NULL,

DNUMBER INTEGER NOT NULL,

MGRSSN CHAR(9),

MGRSTARTDATE CHAR(9),

PRIMARY KEY (DNUMBER),

UNIQUE (DNAME),

FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SNN)

)

slide3

Query 1: Retrieve the birthdate and address of theemployee whose name is 'John B. Smith'.

SELECT BDATE, ADDRESS

FROM EMPLOYEE

WHERE FNAME='John' AND MINIT='B’

AND LNAME='Smith

slide4

Query 2: Retrieve the name and address of all employeeswho work for the 'Research' department.

SELECT FNAME, LNAME, ADDRESS

FROM EMPLOYEE, DEPARTMENT

WHERE DNAME='Research' AND DNUMBER=DNO

slide5

Query 3: For every project located in 'Stafford', list the projectnumber, the controlling department number, and the departmentmanager's last name, address, and birthdate.

SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS

FROM PROJECT, DEPARTMENT, EMPLOYEE

WHERE DNUM=DNUMBER AND MGRSSN=SSN

AND PLOCATION='Stafford'

slide6

Query 4: Make a list of all project numbers for projects that involve anemployee whose last name is 'Smith' as a worker or as a manager ofthe department that controls the project.

(SELECT PNAME

FROM PROJECT, DEPARTMENT,EMPLOYEE

WHERE DNUM=DNUMBER AND

MGRSSN=SSN AND LNAME='Smith')

UNION

(SELECT PNAME

FROM PROJECT, WORKS_ON, EMPLOYEE

WHERE PNUMBER=PNO AND

ESSN=SSN AND NAME='Smith')

slide7

Query 5: Retrieve the name of each employee who has a dependentwith the same first name as the employee.

SELECT E.FNAME, E.LNAME

FROM EMPLOYEE AS E

WHERE E.SSN IN

(SELECT ESSN

FROM DEPENDENT

WHERE ESSN=E.SSN AND

E.FNAME=DEPENDENT_NAME)

slide8

Query 6: Retrieve the name of each employee who has a dependentwith the same first name as the employee.

SELECT E.FNAME, E.LNAME

FROM EMPLOYEE E, DEPENDENT D

WHERE E.SSN=D.ESSN AND

E.FNAME=D.DEPENDENT_NAME

slide9

Query 7: Retrieve a list of employees and the projects

each works in, ordered by the employee's department,

and within each department ordered alphabetically by

employee last name.

SELECT DNAME, LNAME, FNAME,PNAME

FROM DEPARTMENT, EMPLOYEE,

WORKS_ON, PROJECT

WHERE DNUMBER=DNO AND SSN=ESSN

AND PNO=PNUMBER

ORDER BY DNAME, LNAME

slide10

Query 8: Retrieveallemployeeswhoseaddress is in Houston, Texas. Here, the value ofthe ADDRESS attribute must contain thesubstring'Houston,TX‘ in it.

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE ADDRESS LIKE'%Houston,TX%'

slide11

Query 9: Retrieve the name of each employee who workson all the projects controlled by department number 5.

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE ( (SELECT PNO

FROM WORKS_ON

WHERE SSN=ESSN)

CONTAINS

(SELECT PNUMBER

FROM PROJECT

WHERE DNUM=5))

slide12

Query 10: Retrieve the names of employees who have nodependents.

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE NOT EXISTS (SELECT *

FROM DEPENDENT

WHERE SSN=ESSN)

slide13

Query 11: Find the maximum salary, theminimum salary, and the average salary amongemployees who work for the 'Research‘department.

SELECT MAX(SALARY),

MIN(SALARY), AVG(SALARY)

FROM EMPLOYEE, DEPARTMENT

WHERE DNO=DNUMBER AND

DNAME='Research'

slide14

Query 12: For each department, retrieve the departmentnumber, the number of employees in the department, andtheiraveragesalary.

SELECT DNO, COUNT (*),AVG (SALARY)

FROM EMPLOYEE

GROUP BY DNO

slide15

Query 13: For each project, retrieve the project number,

project name, and the number of employees who work onthatproject.

SELECT PNUMBER, PNAME, COUNT (*)

FROM PROJECT, WORKS_ON

WHERE PNUMBER=PNO

GROUP BY PNUMBER, PNAME

slide16

Query 14: For each project on which more thantwo employees work, retrieve the project number,project name, and the number of employees whoworkon thatproject.

SELECT PNUMBER, PNAME,COUNT(*)

FROM PROJECT, WORKS_ON

WHERE PNUMBER=PNO

GROUP BY PNUMBER, PNAME

HAVING COUNT (*) > 2

slide17

Query 15: Specifytheconstraintthatthesalary of an employee must not greaterthanthesalary of themanager of thedepartmentthatthe employee worksfor.

CREATE ASSERTION SALARY_CONSTRAINT

CHECK ( NOT EXISTS ( SELECT *

FROM EMPLOYEE E, EMPLOYEE M,

DEPARTMENT D

WHERE E.Salary>M.Salary

AND E.Dno=D.Dnumber

AND D.Mgr_ssn=M.Ssn ) )

slide18

Query 16: Find the average grade of all ”CS” curriculum students with respect to differentsemesters.

SELECT T.Semester, AVG(T.Grade)

FROM Take AS T, Student AS S

WHERE (S.SID = T.SID) AND (S.Curriculum = "CS")

GROUP BY T.Semester

slide19

Query 17: Find all courses that are offered at least once every year.

SELECT C.Cname

FROM Course AS C

WHERE NOT EXISTS(SELECT distinct(O1.Year)

FROM Offer AS O1

WHERE O1.Year NOT IN

(SELECT O2.Year

FROM Offer AS O2

WHERE (O2.CID = C.CID))

slide20

Query 18: Find all facultieswho taught a course in which the average grade for CS studentswas lower than the other students.

slide21

SELECT F.Fname

FROM Faculty AS F

WHERE F.FID IN (SELECT T1.FID

FROM Take AS T1, Student AS S1

WHERE (T1.SID=S1.SID) AND (S1.Curriculum="CS")

GROUP BY T1.FID

HAVING Avg(T1.Grade) <all

(SELECT Avg(T2.Grade)

FROM Take AS T2, Student AS S2

WHERE (T2.SID=S2.SID) AND (S2.Curriculum <>"CS")

AND (T1.FID = T2.FID)

AND (T1.CID = T2.CID)

AND (T1.Semester = T2.Semester)

AND (T1.Year = T2.Year)

GROUP BY T2.FID)

slide22

Query 19: Find the number and department identifier of all courses in which no student ever got an ’F’.

SELECT C.course_number, C.dept_id

FROM COURSE C

WHERE NOT EXISTS

(SELECT *

FROM TRANSCRIPT T, SECTION S

WHERE (T.grade = ’F’) AND (T.section_id=S.section_id)

AND (S.course_number=C.course_number)

AND (S.dept_id = C.dept_id)

slide23

Query 20: Find the id of all sections of courses offered by department ”Computer Science” in the ”Fall99” semester.

SELECT S.section_id

FROM section S, department D

WHERE S.dept_id = D.dept_id AND D.department_name= "Computer Science" ANDS.semester= "Fall99"

slide24

Query 21: Find the id of all sections that a student named ”Kenny” is taking in ”Fall99” semester.

SELECT T.section_id

FROM section S, transcript T, student StWHERE St.sid = T.sid AND S.section_id = T.section_id ANDSt.firstname= "Kenny" AND S.semester "Fall99"

slide25

Query 22: Find the first name, last name and the department name for all instructors who are teaching atleast three sections of a single course in semester ’Fall 99’.

SELECT I.firstname, I.lastname, D.department_name

FROM INSTRUCTOR I, DEPARTMENT D

WHERE (I.dept_id = D.dept_id) AND

EXISTS

(SELECT S.course_number, S.dept_id

FROM SECTION S

WHERE (S.semester = ’Fall 99’) AND

(S.instructor_id= I.instructor_id)

GROUP BY S.course_number, S.dept_id

HAVING count(S.section_id) >= 3)

slide26

Query 23: For all different courses in the curriculum, find the total number of students in that class foreach different semester (regardless of their sections).

SELECT SC.course_number, SC.dept_id, SC.semester, count(TR.sid)

FROM SECTION SC, TRANSCRIPT TR

WHERE SC.section_id = TR.section_id

GROUP BY SC.course_number, SC.dept_id, SC.semester

slide27

Query 24: Find the average number of students in each different course (identified by course numberand dept id), average over different semesters. Find for each course number of students for differentsemesters and then take the average.

SELECT S.course_number, S.dept_id, count(S.sid)/count(DISTINCT S.semester)

FROM section S, transcript T

WHERE S.section_id = T.section_id

GROUP BY S.course_number, S.dept_id

slide28

Query 25: Set the grade of student named ”Kenny” for course number 111, dept id 15 offeredin ”Fall99” to ”F”.

UPDATE transcript

SET grade = ’F’

WHERE sidIN

(SELECT S.sid

FROM student S

WHERE S.firstname = ’Kenny’) ANDsection_idIN

(SELECT Sc.section_id FROM section Sc

WHERE Sc.course_number = 111 AND Sc.dept_id = 15 ANDSc.semester= ’Fall99’)

slide29

Query 26: Insert a tuple into transcript indicating that ”Kenny” is taking course number 111,deptid 15 in ”Fall99”.

INSERT INTO transcript(sid, section_number)

SELECT DISTINCT S.sid, Sc.section_number

FROM student S, section Sc

WHERE S.firstname = ’Kenny’ AND Sc.dept_id = 15 ANDSc.course_number= 111 AND Sc.semester= ’Fall99’