The document describes a series of tasks involving creating tables, entering data, and running queries on several databases. For Part A, it involves creating tables for students, a library, employee salaries, and an insurance database, then performing queries like updating records, finding totals, and joining tables. For Part B, similar creation and querying tasks are described for tables involving employees, students, student marks, and customers.
The document describes a series of tasks involving creating tables, entering data, and running queries on several databases. For Part A, it involves creating tables for students, a library, employee salaries, and an insurance database, then performing queries like updating records, finding totals, and joining tables. For Part B, similar creation and querying tasks are described for tables involving employees, students, student marks, and customers.
The document describes a series of tasks involving creating tables, entering data, and running queries on several databases. For Part A, it involves creating tables for students, a library, employee salaries, and an insurance database, then performing queries like updating records, finding totals, and joining tables. For Part B, similar creation and querying tasks are described for tables involving employees, students, student marks, and customers.
The document describes a series of tasks involving creating tables, entering data, and running queries on several databases. For Part A, it involves creating tables for students, a library, employee salaries, and an insurance database, then performing queries like updating records, finding totals, and joining tables. For Part B, similar creation and querying tasks are described for tables involving employees, students, student marks, and customers.
PART - A A1. The STUDENT detail databases has a table with the following attributes. The primary keys are underlined. STUDENT(regno: int, name: string, dob: date, marks: int) i) Create the above table. ii) Remove the existing attribute marks from the table. iii) Change the date type of regno from integer to string. iv) Add a new attribute phoneno to the existing table. v) Enter five tuples into the table. vi) Display all the tuples in student table.
A2. A LIBRARY database has a table with the following attributes.
LIBRARY(bookid:int, title:string, author:string, publication:string, yearpub:int, price:real) i) Create the above table. ii) Enter the five tuples into the table iii) Display all the tuples in Library table. iv) Display the different publishers from the list. v) Arrange the tuples in the alphabetical order of the book titles. vi) List the details of all the books whose price ranges between Rs. 100 and Rs. 300
A3. The SALARY database of an organization has a table with the following attributes. EMPSALARY(empcod:int, empname:string, dob:date, department:string, salary:real) i) Create the above table. ii) Enter the five tuples into the table iii) Display all the number of employees working in each department. iv) Find the sum of the salaries of all employees. v) Find the sum and average of the salaries of employees of a particular department. vi) Find the least and highest salaries that an employee draws.
A4. Consider the insurance database given below. The primary keys are underlined and the data types are specified. PERSON(driver-id-no: string, name: string, address: string) CAR(regno: string, model: string, year: int) ACCIDENT(report-no: int, date: date, location: String) OWNS(driver-id-no: string, regno: string) PARTICIPATED(driver-id-no: string, regno: string, report-no: int, damage-amount: int) i) Create the above tables by properly specifying the primary keys and the foreign keys ii) Enter atleast five tuples for each relation. iii) Demonstrate how you a) Update the damage amount for the car with a specific regno in the accident with report no 102 to 25000. b) Add a new accident to the database. iv) Find total number of people who owned cars that were involved in accidents in 2013 v) Find the number of accidents in which cars belonging to a specific model were involved DATABASE MANAGEMENT SYSTEM LAB
PART - B B1. EmpMaster(Empcode,Ename,Deptname)
a) Create the above table.
b) Enter 5 rows. c) Create table empmaster1 as the structure similar to empmaster table without records. d) Create table empmaster2 as the structure similar to empmaster table with records. e) Display all records of empmaster, empmaster1, empmaster2.
B2. EmpSalary(Empcode,Ename,Salary)
a) Create the above table.
b) Enter 5 rows. c) Display only those rows whose Salary ranges between 5000 and 8000. d) List all employees name, salary and 15% rise in salary e) Display the employee name whose name start with ‘A’.
B3. Students(Regno,Name,Course,Sem)
a) Create the above tables.
b) Enter 5 rows. c) List the Name in Uppercase of Students. d) List all the details of Students order by Name. e) List Regno,Name,Sem of Students by decreasing Regno.
B4. StudentMarks(Regno,Name,Course,Marksobtained)
a) Create the above tables.
b) Enter 5 rows. c) List all the students from the Course BCA,BA. d) List the Average marks, Total marks from the table. e) List Maximum and Minimum marks from the table.
B5. CustomerDetail(CustomerID,Name,Dept,City)
a) Create the above tables.
b) Enter 5 rows. c) Display the Name as CustomerName in uppercase and City in lowercase from the table. d) Display the name and city in single column. e) Display distinct City from the table. DATABASE MANAGEMENT SYSTEM LAB
PART – A A1. STUDENT(regno: int, name: string, dob: date, marks: int) i) Create the above table. ii) Remove the existing attribute marks from the table. iii) Change the date type of regno from integer to string. iv) Add a new attribute phoneno to the existing table. v) Enter five tuples into the table. vi) Display all the tuples in student table. DATABASE MANAGEMENT SYSTEM LAB DATABASE MANAGEMENT SYSTEM LAB
A2. A LIBRARY database has a table with the following attributes.
LIBRARY(bookid:int, title:string, author:string, publication:string, yearpub:int, price:real) i) Create the above table. ii) Enter the five tuples into the table iii) Display all the tuples in Library table. iv) Display the different publishers from the list. v) Arrange the tuples in the alphabetical order of the book titles. vi) List the details of all the books whose price ranges between Rs. 100 and Rs. 300 DATABASE MANAGEMENT SYSTEM LAB DATABASE MANAGEMENT SYSTEM LAB
A3. The SALARY database of an organization has a table with the following attributes. EMPSALARY(empcod:int, empname:string, dob:date, department:string, salary:real) i) Create the above table. ii) Enter the five tuples into the table iii) Display all the number of employees working in each department. iv) Find the sum of the salaries of all employees. v) Find the sum and average of the salaries of employees of a particular department. vi) Find the least and highest salaries that an employee draws. DATABASE MANAGEMENT SYSTEM LAB DATABASE MANAGEMENT SYSTEM LAB
A4. Consider the insurance database given below. The primary keys are underlined and the data types are specified. PERSON(driver-id-no: string, name: string, address: string) CAR(regno: string, model: string, year: int) ACCIDENT(report-no: int, date: date, location: String) OWNS(driver-id-no: string, regno: string) PARTICIPATED(driver-id-no: string, regno: string, report-no: int, damage-amount: int) i) Create the above tables by properly specifying the primary keys and the foreign keys ii) Enter at least five tuples for each relation. iii) Demonstrate how you b) Update the damage amount for the car with a specific regno in the accident with report no 103 to 25000. b) Add a new accident to the database. iv) Find total number of people who owned cars that were involved in accidents in 2013 v) Find the number of accidents in which cars belonging to a specific model were involved DATABASE MANAGEMENT SYSTEM LAB DATABASE MANAGEMENT SYSTEM LAB DATABASE MANAGEMENT SYSTEM LAB
Update the damage amount for the car with a specific regno in the accident with report no 102 to 25000.
Add a new accident to the database.
Find total number of people who owned cars that were involved in accidents in 2013
Find the number of accidents in which cars belonging to a specific model were involved DATABASE MANAGEMENT SYSTEM LAB
A5. Consider the following database of students enrollment in courses and books adopted for each course. STUDENT(regno: string, name: string, major: strong, bdate: date) COURSE(course-no: int cname: string, dept: string) ENROLL(reg-no: string, course-no: int, sem: int, marks: int) BOOK-ADOPTION(course-no: int, sem: int, book-isbn: int) TEXT(book-isbn: int, book-title: string, publisher: string, author: string)
i) Create the above tables by properly specifying the primary keys and the foreign keys ii) Enter atleast five tuples for each relation. iii) Demonstrate how you add a new text book to the database and make this book be adopted by some department. iv) List any department that has all its adopted books published by a specific publisher. DATABASE MANAGEMENT SYSTEM LAB DATABASE MANAGEMENT SYSTEM LAB
Find all the customers who have atleast two accounts at the main branch.
Find all customer who have an account at all the branches located in a specific city.
Demonstrate how to delete all account tuples at every branch located in specific city. DATABASE MANAGEMENT SYSTEM LAB
PART – B B1. EmpMaster(Empcode,Ename,Deptname)
a)Create the above table.
b) Enter 5 rows. c) Create table empmaster1 as the structure similar to empmaster table without records. d) Create table empmaster2 as the structure similar to empmaster table with records. e) Display all records of empmaster, empmaster1, empmaster2.
a)
b)
c)
d) DATABASE MANAGEMENT SYSTEM LAB
e) DATABASE MANAGEMENT SYSTEM LAB
a) Create the above table.
b) Enter 5 rows. c) Display only those rows whose Salary ranges between 5000 and 8000. d) List all employees name, salary and 15% rise in salary e) Display the employee name whose name start with ‘A’
a)
b)
c) DATABASE MANAGEMENT SYSTEM LAB
d)
e) DATABASE MANAGEMENT SYSTEM LAB
B3. Students(Regno,Name,Course,Sem)
a) Create the above tables.
b) Enter 5 rows. c) List the Name in Uppercase of Students. d) List all the details of Students order by Name. e) List Regno,Name,Sem of Students by decreasing Regno.
a)
b) DATABASE MANAGEMENT SYSTEM LAB
c)
d)
e) DATABASE MANAGEMENT SYSTEM LAB
B4. StudentMarks(Regno,Name,Course,Marksobtained)
a) Create the above tables.
b) Enter 5 rows. c) List all the students from the Course BCA,BA. d) List the Average marks, Total marks from the table. e) List Maximum and Minimum marks from the table.
a)
b) DATABASE MANAGEMENT SYSTEM LAB
c)
d)
e) DATABASE MANAGEMENT SYSTEM LAB
B5. CustomerDetail(CustomerID,Name,Dept,City)
a) Create the above tables.
b) Enter 5 rows. c) Display the Name as CustomerName in uppercase and City in lowercase from the table. d) Display the name and city in single column. e) Display distinct City from the table.
