Scribd
Upload
65 views

DBMS Lab Assignment 04

This document provides instructions for creating and manipulating tables in a database management system. It includes creating two tables, TEAM and RUN, with player and scoring data. It then provides various queries to select, update, delete and group data from the tables, including calculating averages and modifying table and record attributes. It also creates a second table, EMPLOYEE, with employee data and performs similar operations on it.

Uploaded by

Sachin Gaikwad
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
65 views

DBMS Lab Assignment 04

This document provides instructions for creating and manipulating tables in a database management system. It includes creating two tables, TEAM and RUN, with player and scoring data. It then provides various queries to select, update, delete and group data from the tables, including calculating averages and modifying table and record attributes. It also creates a second table, EMPLOYEE, with employee data and performs similar operations on it.

Uploaded by

Sachin Gaikwad
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Database Management System (DBMS) Lab Assignment 04

1. Create the following Table:


A. Table Name : TEAM
Column Name Data Type Size Constraints
Jersy_No Integer 10 Primary Key
Player_Name Varchar 30 Not Null
Team Varchar 20
Data for TEAM Table:
Jersy_No Player_Name Team
11 Tendulkar India
23 Malinga SriLanka
15 Akram Pakistan
16 Dravid India
17 Chopra India

B. Table Name : RUN


Column Name Data Type Size Constraints
Jersy_No Integer 10 Foreign Key references
Jersy_No of TEAM Table
Player_Name Varchar 30 Not Null
Against Varchar 20
Score Integer 10 Score>0
Data for RUN Table:
Jersy_No Player_Name Against Score
11 Tendulkar SriLanka 89
23 Malinga India 58
15 Akram India 80
15 Akram SriLanka 65
23 Malinga India 38
11 Tendulkar Pakistan 126

2. Display the structure of all the tables.

3. Display the Jersy_No (in descending order) and Player_Name from RUN Table (use distinct).
4. Change the attribute names in RUN Table:
“Jersy_No” as “Player_No” and “Against” as “Against_Team”.
Display all the records of RUN table.
5. Display all the records of RUN table where the field Player_No is 48.
6. Display all the records of RUN table where the field Player_No is 11.

7. By using concat show the table in the form of sentence, like:


Tendulkar has scored 126 against Pakistan.
8. Add “BirthDay” column in TEAM Table with appropriate data type. Insert the values by yourself.
9. Display the Players Name from TEAM where ‘g’ is present in Player_Name.
10. Display the Players Name from TEAM where ‘g’ is present in second letter of Player_Name or ‘k’
is present in second letter of Player_Name.
11. Display the Players Name from TEAM where Player_Name start with ‘T’.
12. Display all the records from RUN where Player_Name start with ‘T’ and end with ‘r’.
13. Change the name of the player “Tendulkar” to “S-Tendulkar” in TEAM table and RUN table.
14. Select player names from RUN table where India is Against_Team and Score>60.

15. Display all the records from TEAM where Player_No is in between 10 and 20 with order.
16. Delete the records of 16 Player_No from TEAM .
17. Drop column BirthDay from TEAM table.
18. Insert the following values in TEAM:
12,’Virat’,’India’
13,’Dhoni’,’India’

18,’Shoaib’,’Pakistan’
19. Find out the average score of S-Tendulkar.
20. Select Player_Name and average Score from RUN by grouping Player_Name where average
score>50.

21. Create the following Table:


A. Table Name : EMPLOYEE
Column Name Data Type Size Constraints
Emp_Id Varchar 10 Primary Key
Emp_Name Varchar 20 Not Null
Department Varchar 15
Salary Integer 10
DOJ Date
Branch Varchar 10
Data for EMPLOYEE Table:
Emp_Id Emp_Name Department Salary DOJ Branch
EID201 Sandeep COMPUTER 365000 2010-03-12 Bangalore
EID202 Baibhav CIVIL 270000 2002-08-03 Bangalore
EID204 Anshuman MECHANICAL 420000 2001-09-11 Kolkata
EID206 Ravi ELECTRICAL 397000 2011-01-01 Mysore
EID208 Rahul COMPUTER 505000 2005-12-01 Hyderabad

22. Display the structure of EMPLOYEE table.


23. Show Employee Name and their salary (salary in descending order).
24. Retrieve average salary of all Employees.

25. Retrieve name and salary of Employees where salary is greater than >300000.
26. Retrieve total number of Employee.
27. Retrieve id, name and department of the Employee where DOJ is ‘2011-01-01’.
28. Display details of Employee whose department name start with ‘CO’.
29. Delete all records from EMPLOYEE table. (Use Turncate)
30. Delete the table EMPLOYEE.

You might also like