How to Effectively Write SQL Queries Based on the Given Scenario

sql, queries, solution, scenario

Part 1:

Complete the tables you created in Part II by adding data type to each attribute and state whether the attribute is mandatory or optional (NULL or NOT NULL), identify primary key and foreign key.

Part 2:

Student           (matricNo, studentFName, studentLName, street, city, postcode, DOB, sex, category, nationality, smoker, spcialneeds, comments, status, courseNo)

Primary Key matricNo

Next-of-kin     (matricNo, lName, fName, street, city, postcode, contactNo)

Primary Key matricNo

Course            (courseNo, courseTile, courseLeader, courseLeaderTelNo, courseLeaderRoomNo,deptName)

Primary Key courseNo

Foreign Key courseLeaderRoomNo references Room(placeNo)

Room              (placeNo, roomNo, monthlyRent, flatNo, hallNo)

Primary Key placeNo

Foreign Key monthlyRent references Invoice(invoiceNo)

Foreign Key flatNo references Flat(flatNo)

Foreign Key hallNo references Hall(hallNo)

Lease              (leaseNo, duration, matricNo, placeNo, dateEnter, dateLeave)

Primary Key leaseNo

Foreign Key matricNo references Student(matricNo)

Foreign Key placeNo references Room(placeNo)

Foreign Key dateLeave references flatInspection(inspDate)

Invoice            (invoiceNo, semester, paymentDue, datePaid, paymentMethod, dateReminder1, dateReminder2, leaseNo)

Primary Key invoiceNo

Foreign Key leaseNo references Lease(leaseNo)

Hall                 (hallNo, hallName, hallAddress, telNo, mgrstaffNo)

Primary Key hallNo

Foreign Key mgrstaffNo references AccommStaff(staffNo)

AcommodationStaff              (staffNo, fName, lName, street, city, postcode, DOB, sex, position, location)

Primary Key staffNo

Foreign Key location references Room(placeNo)

FlatInspection            (flatNo, inspDate, condition, comments, inspStaffNo)

Primary Key flatNo, inspDate

Foreign Key inspStaffNo references AccommStaff(staffNo)

Flat                 (flatNo, flatAddress, noOfRooms)

Primary Key flatNo

  1. Implement the database in SQL Server
    1. build a database and name it as UCODB1 for group1 and ucoDB2 for group2…,
    2. create a set of relations (TABLES),
    3. populate them with example data,
    4. test the sample query transactions in B.1.2. Make sure your database works with all of these sample query transactions

Solution:

1- select m.fname , h.telNo from AcommodationStaff as m inner join
Hall as h on m.staffNo = h.mgrstaffNo;

2- select s.studentFName ,s.courseNo , l.* from Student as s inner join
Lease as l on s.matricNo = l.matricNo;

3- select  * from Lease WHERE DATEDIFF(ww , dateEnter , dateLeave) > 16 and DATEDIFF(ww , dateEnter , dateLeave) < 35

4- SELECT *  FROM [UCODB1].[dbo].[FlatInspection]  where  [condition] like ‘unsatisfactory’;

5- select   s.studentFName , r.placeNo , r.monthlyRent , r.roomNo from Student as s inner join
Lease as l on s.matricNo = l.matricNo inner join Room r on
r.placeNo = l.placeNo

6- select   s.studentFName , r.placeNo , r.monthlyRent , r.roomNo from Student as s inner join
Lease as l on s.matricNo = l.matricNo left join Room r on
r.placeNo = l.placeNo

7- select   count(*) from Student where category like ‘std’;

8- select   s.studentFName from Student s left join Nextofkin as n
on s.matricNo = n.matricNo  

9 – select   h.hallNo ,  Avg(r.monthlyRent) , MAX(r.monthlyRent) ,    
MIN(r.monthlyRent)  from Hall h inner join
Room as r on h.hallNo = r.hallNo GROUP BY h.hallNo , r.monthlyRent

10 – select   h.hallNo , r.placeNo   from Hall h inner join
Room as r on h.hallNo = r.hallNo

11 –  
select   s.staffNo , s.fName , s.location   from AcommodationStaff s inner join
Room as r on s.staffNo = r.placeNo where CONVERT(int,ROUND(DATEDIFF(hour,s.DOB,GETDATE())/8766.0,0)) > 60; 

LEAVE A REPLY

Please enter your comment!
Please enter your name here