Chitika

Lab Assignments

A) Define the schema for the following databases with specific data type and constraints, the table name and its fields name are to be taken from database description which are given below :
Q.1. A database is being constructed for storing sales information system. A product can be described with a unique product number, product name, selling price, manufacturer name. The product can sale to a particular client and each client have it own unique client number, client name, client addresses, city, pin code, state and total balance to be required to paid. Each client order to buy product from the salesman. In the order, it has unique sales order number, sales order date, client number, salesman number (unique), billed whole payment by the party or not and its delivery date. The salesman have the name, addresses, city, pin code, state, salary of the sales man, delivery date, total quantity ordered, product rate.

Q.1.1. Write the SQL queries for the following –

  1. Retrieve the list of names and the cities of all the clients.
  2. List the various products available.
  3. Find the names of all clients having ‘a’ as the second letter in their names.
  4. List all the clients who are located in Colombo.
  5. Find the products whose selling price is greater than 2000 and less than or equal to 5000
  6. Add a new column NEW_PRICE into the product_master table.
  7. Rename the column product_rate of Sales_Order_Details to new_product_rate.
  8. List the products in sorted order of their description.
  9. Display the order number and date on which the clients placed their order.
  10. Delete all the records having delivery date before 25th August, 2010.
  11. Change the delivery date of order number ON01008 to 16-08-10
  12. Change the bal_due of client_no CN01003 to 1200
  13. Find the product with description as ‘HDD1034’ and ‘DVDRW’
  14. List the names, city and state of the clients not in the city of ‘Kandy’
  15. List of all orders that were canceled in the month of March.
Q.2. A student is described by a unique Roll Number, Name Address, and Semester. Each student enrolls himself in an Academic programme offered by a Department. Academic programmes have programme name(unique), duration, a programme code(unique) and a list of courses (both core and elective course) while the departments have department code (unique), department name (unique), HoD who is a Teacher and list of courses offered by it. Each teacher is described by employee code (unique), name, department and designation. A student registers some courses in a semester. A course is described by a unique course number, title of the course, credit allotted for the course and offering department. Database stores the grades obtained by different student in different courses registered by him/her in different semesters. Database also stores information about the courses offered by a department in a semester, the corresponding teacher(s) for each course.

Q.2.1. Write the SQL queries for the following –

  1. Find all the students’ name, city, course allotted from the CSE department.
  2. List the total number of Faculty in the CSE department.
  3. List the available courses from the CSE department.
  4. List the all students in a particular semester.
  5. List the students who earned a GPA greater than or equal to 8.5
  6. How much subjects are registered by a student in each semester.
  7. List the common students who are allotted the same courses of both the programme MCA and M.Tech.
  8. List the total number of student enrolled in the subject DBMS.
  9. Retrieve the semester of the student under DBMS subject.
  10. Retrieve all the student name and arrange into ascending order.
  11. Modify a student address Colombo to Gampaha where sdt_id=’CSI08002’.
  12. Find the total credit point of student required to complete for a course like MCA.
  13. List the all courses which are related to computer science.
  14. Retrieve all the students located at ‘Colombo’.
  15. Find the total number of department in our database.
Q.3. Payroll system of Tezpur University

Q.3.1. Write the queries for the following –

  1. List all the employees of CSE department.
  2. Retrieve all the employees who have the gross salary greater than or equal to Rs-21,000.
  3. Find the Date Of Birth, Date Of Hire of the employee name ‘Rob’.
  4. Find the total leave get by an employee.
  5. List the employees department.
  6. List the name grades of the employee of the CSE department.
  7. How much tax will be required to pay by an employee in each month?
  8. List the employees who are joined between ’10-APR-07’ to ’28-AUG-08’.
  9. Retrieve the mail address of all employees of CSE department.
  10. Total leave available of the employee ‘Rohith’.
  11. Add a new column ‘employee phone’ to a table employee.
  12. List the employees with basic salary 8000.
  13. Find the top 10 employees who have the highest salaries.
  14. Retrieve the employees who have bank loan.
  15. Find the bank balance of the employee ‘CSEMP07001’.
Q. 4: Bank Database:

A bank database keeps record of the details of customers, accounts, loans and transactions such as deposits or withdraws. Customer record should include customer id, customer name, address, age, contact number, email id etc., accounts details involves account number, account type(fixed account, savings account, monthly account etc), date of creation of the account. Transaction detail keeps information about amount deposited or withdrawn to/from a particular account and the date of transaction. The database should also store record of loans which include loan amount, loan date and the account number to which the loan is granted. Make appropriate tables for the above database and try to find out the following queries:

  1. List the details of account holders who have a ‘savings’ account.
  2. List the Name and address of account holders with loan amount more than 50,000.
  3. Change the name of the customer to ‘ABC’ whose account number is ’TU001’
  4. List the account number with total deposit more than 80,000.
  5. List the number of fixed deposit accounts in the bank.
  6. Display the details of customers who created their accounts between ’20-jan-08’ to ’20-aug-08’.
  7. Display the detailed transactions on 28th Aug, 2008.
  8. Display the total amount deposited and withdrawn on 29th Aug, 2008.
  9. List the details of customers who have a loan.
Q.5. Library information system:

Database should store information about books, journals, magazines etc. Searching for books can be done by author, title, and subject. Similarly journals can be searched by subject area, publisher etc. It should also be possible to see which book is issued to which student and belonging department.

  1. List the names of the books issued between 21-aug-08 and 29-Aug-08.
  2. Retrieve the name and number of books by a particular author.
  3. Retrieve the name of the publisher which has maximum number of books.
  4. Count the total number of books in the library.
  5. Count the number of books issued to a student with Roll no ‘CSB06001’
  6. Change the author of the book to ‘ABC’ with book id=’BK003’.
  7. Retrieve the name of the student to whom the book named ‘Database System’, by ‘E.Navathe’ is issued.
  8. Display the total number of books issued to different departments.
  9. List the name of the books where subject is like ‘ora’.
Q.6 Hospital information system:

Patients - indoor/outdoor, medicines/lab tests(including results) prescribed to patients, information if a patient if referred to other expert/hospital. Doctors - specialization, patients attended etc. Different wards/beds and patients allotted to them etc. Patient registration form should include Registration number, Patient name, Address, Gender, Bed number, date of registration, refer doctor id etc. Doctor information should include Doctor code, Doctor Name, Specialization etc. Lab test information should include Test name, test number, test date, results and referred doctor’s code. Bed information should include bed number, ward number and status (whether allotted or not). Queries:

  1. Display the details of patients admitted between ‘20-jul-02’ and ’20-aug-08’.
  2. Change the name of the patient to ‘Ram’ whose patient id=’PT011’
  3. Display the names of the patients and lab test results performed on ‘20-jul- 08’.
  4. Display the number of patients taking treatment under doctor =’ABC’.
  5. Retrieve the name of doctor who is taking care of maximum number of patients.
  6. Change the bed number of the patient to 456 where patient id=’PT023’
  7. Change the status of bed with bed number 123 with ‘not allotted’.
  8. List the bed details which are free in ward number 10.
  9. List the name of male patients in ward no 13 taking treatment under doctor ‘XYZ’
  10. List the details of patients with age more than 50 taking treatment under a doctor, whose name like ‘das’.
Q7 What will be returned by the following code?


USE MASTER
GO
CREATE PROCEDURE sp_ReturnSomething (@Input INT)
AS
BEGIN
SELECT @Input + 2 AS Result
END
GO
-----------------------------------------
Use AdventureWorks
GO
CREATE PROCEDURE Person.sp_ReturnSomething (@Input INT)
AS
BEGIN
SELECT @Input AS Result
END
GO

EXECUTE sp_ReturnSomething 5
-----------------------------------------
DROP procedure Person.sp_ReturnSomething
GO
USE MASTER
GO
DROP PROCEDURE dbo.sp_ReturnSomething
GO

Q8.
Assume that the following tables are created in your current database


Employee(EmpID int, EName varchar(20), DOB datetime,address varchar(50), contactNo varchar(10))

Orders(orderID int primary key,customerID int,description varchar(20), delivery_Date datetime)

1. Write a sql constraint to set the EmpID as the primary key of the Employee table

2. Write a sql constraint to check the DOB value is between 01-01-1950 and 01-01-1990

3. Write a sql constraint to set a default value ‘UNKNOWN’ for contactNo field.

4. customerID appears as the primary key of a table called customers in the same database, write a constraint to set the customerID in the Orders table as a foreign key.

Q9

Assume that you have a web site created and visiters can register with the site by giving there personal details including the email address, and user name.these personal details are inserted into a tabale called members (assume that table is already created).upon inserting a member record to the table you want to send a welcome email to the member automatically.


Write a sql script to perform the above operation.(consider that the SQL Server surface area congiguration is configured properly).

Q10.
a) Write SQL queries for the following operations


1. Create a login : login ID ‘surgeon‘ and the password ‘spsw123*’

2. Create a database schema ‘ops’ and a table called ‘theatre’ within the schema. Table columns are as follows

a. theatreID - int,primarykey,auto increment by 10 starting from 1000.

b. Incharge – int(foreign key ref. doctorID from doctors table)

c. Location – varchar(20)

d. Equipments – varchar(50)

e. Facilities – varchar(50)

3. create a database user with the same name as a login ID and assign the default schema ‘ops’

4. Give permissions to INSERT, UPDATE, and DELETE records on the table theatre to the above user.

5. Give permissions to execute any stored procedure in the above schema ‘ops’ to the above user.

6. Add the above user to the fixed server role ‘sysadmin’

7. Add the above user to the fixed database role ‘db_owner’

8. Create a role called ‘doctor’ and assign permissions INSERT, UPDATE, DELETE and VIEW on the table ‘theatre’.

b) Explain the following fixed server/database roles exists in SQL Server 2005

1. sysadmin

2. securityadmin

3. dbcreator

4. db_owner

5. db_accessadmin

6. db_securityadmin



Subscribe to: Posts (Atom)