Download as PDF, PPTX
More Related Content
![Data Structures - Lecture 7 [Linked List]](https://cdn.slidesharecdn.com/ss_thumbnails/lecture-7linkedlists-150121011916-conversion-gate02-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
Similar to Relational Database Managment System Lab - Group A
Relational Database Managment System Lab - Group A
- 1. R.Mathan - Bsc.CS(2022-2025) AlagappaGovernment Arts College, Karaikudi. Dr. S.Murugan, Associate Professor Department of Computer Science, AlagappaGovernment Arts College, Karaikudi. (Affiliated by AlagappaUniversity) Mailid: muruganjit@gmail.com
- 2. Ex. No: 1.Create a student table with the following attributes name, register number, department, marks in 5 subjects and total. (a) Insert few records into student table. (b) Display all the records (c) Calculate the total marks for all the records. (d) Display the information of student name, register number and total only.
- 3. Create a studenttable with the following attributes name, register number, department, marks in 5 subjects and total. SQL> create table stud36 ( 2 name char(30), 3 regno number(10), 4 department char(10), 5 m1 number(5), 6 m2 number(5), 7 m3 number(5), 8 m4 number(5), 9 m5 number(5), 10 total number(5) ); Table created.
- 4. (a) Insert fewrecords into student table. SQL> insert into stud36 values('aakash',2230,'CSE',78,89,79,67,90,''); 1 row created. SQL> insert into stud36 values('gugan',2231,'CSE',88,69,79,57,70,''); 1 row created. SQL> insert into stud36 values('gurudev',2232,'EEE',68,89,79,67,70,''); 1 row created. SQL> insert into stud36 values('kesav',2233,'ECE',88,89,89,67,60,''); 1 row created. SQL> insert into stud36 values('mathan',2234,'CSE',88,89,79,77,80,''); 1 row created.
- 5. (b) Display allthe records SQL> select * from stud36; NAME REGNO DEPARTMENT M1 M2 M3 M4 M5 TOTAL ---------- ---------- --------------- ------ ------- -------- ------- ------- ---------- aakash 2230 CSE 78 89 79 67 90 gugan 2231 CSE 88 69 79 57 70 gurudev 2232 EEE 68 89 79 67 70 kesav 2233 ECE 88 89 89 67 60 mathan 2234 CSE 88 89 79 77 80
- 6. (c) Calculate thetotal marks for all the records. SQL> update stud36 set total=m1+m2+m3+m4+m5 where regno>0; 5 rows updated. (d) Display the information of student name, register number and total only. SQL> select name,regno,total from stud36; NAME REGNO TOTAL -------------------------- ---------- ---------- aakash 2230 403 gugan 2231 363 gurudev 2232 373 kesav 2233 393 mathan 2234 413
- 7. Ex. No: 2.Create a student table with the following attributes name, registernumber, department, marks in 5 subjects and total. (a) Insert few records into student table. (b) Modify the name of the student as vignesh whose register number is 211278019. (c) Delete the records whose register number is 211278005. (d) Display all the records.
- 8. Create a studenttable with the following attributes name, registernumber, department, marks in 5 subjects and total. SQL> create table stud_36 ( 2 name char(30), 3 regno number(10), 4 department char(10), 5 m1 number(5), 6 m2 number(5), 7 m3 number(5), 8 m4 number(5), 9 m5 number(5), 10 total number(5) ); Table created.
- 9. (a) Insert fewrecords into student table. SQL> insert into stud_36 values('mathan',211278001,'CSE',88,89,79,77,80,''); 1 row created. SQL> insert into stud_36 values('kesav',211278002,'ECE',90,88,60,77,71,''); 1 row created. SQL> insert into stud_36 values('sakthi',211278005,'EEE',90,99,60,61,70,''); 1 row created. SQL> insert into stud_36 values('mohan',211278019,'ECE',80,88,59,68,90,''); 1 row created. SQL> insert into stud_36 values('raj',211278020,'MECH',70,88,68,90,81,''); 1 row created.
- 10. Display the records: SQL>select * from stud_36; NAME REGNO DEPARTMENT M1 M2 M3 M4 M5 TOTAL ---------- ---------- ----------------- -------- ------- ---------- -------- ------ ---------- mathan 211278001 CSE 88 89 79 77 80 kesav 211278002 ECE 90 88 60 77 71 sakthi 211278005 EEE 90 99 60 61 70 mohan 211278019 ECE 80 88 59 68 90 raj 211278020 MECH 70 88 68 90 81 (b) Modify the name of the student as vignesh whose register number is 211278019. SQL> update stud_36 set name='vignesh' where regno=211278019; 1 row updated.
- 11. ( c) Deletethe records whose register number is 211278005. SQL> delete from stud_36 where regno=211278005; 1 row deleted. SQL> select * from stud_36; NAME REGNO DEPARTMENT M1 M2 M3 M4 M5 TOTAL ---------- ---------- ----------------- -------- ------- ---------- -------- ------ ---------- mathan 211278001 CSE 88 89 79 77 80 kesav 211278002 ECE 90 88 60 77 71 vignesh 211278019 ECE 80 88 59 68 90 raj 211278020 MECH 70 88 68 90 81 Upadte the total: SQL> update stud_36 set total=m1+m2+m3+m4+m5 where regno > 0; 4 row updated.
- 12. (d) Display allthe records. SQL> select * from stud_36; NAME REGNO DEPARTMENT M1 M2 M3 M4 M5 TOTAL ---------- ----------- ------------------ ------- ------- ------- ------ -------- ---------- mathan 211278001 CSE 88 89 79 77 80 413 kesav 211278002 ECE 90 88 60 77 71 386 vignesh 211278019 ECE 80 88 59 68 90 385 raj 211278020 MECH 70 88 68 90 81 397
- 13. Ex. No: 3.Create a table student with name, roll number, gender, age and mobile number. Apply the following integrity rules to the student table. (a) The student name must be in capital letter. (b) The roll number must be greater than zero. (c) The age cannot be a null value. (d) The gender must be “Male” or “Female” or “Transgend” (e) The mobile number may contain null values.
- 14. Create a tablestudent with name, roll number, gender, age and mobile number. SQL> create table stud_info ( 2 name char(30) not null check(name=upper(name)), 3 roll_no number(10) not null check(roll_no>0), 4 gender char(30) not null check(gender in('male','female','transgend')), 5 age number(10) not null, 6 mobileno number(10) null 7 ); Table created.
- 15. (a) The studentname must be in capital letter. SQL> insert into stud_info values('MATHAN',8001,'male',20,6381761984); 1 row created. Verification for name must be in capital letter : SQL> insert into stud_info values('mathan',8001,'male',20,6381761984); insert into stud_info values('mathan',8001,'male',20,6381761984) * ERROR at line 1: ORA-02290: check constraint (SYSTEM.SYS_C004048) violated
- 16. (b) The rollnumber must be greater than zero. SQL> insert into stud_info values('RAJ',8002,'male',19,9176198010); 1 row created. Verification for roll number must be grater than zero : SQL> insert into stud_info values('RAJ',-8002,'male',19,9176198010); insert into stud_info values('RAJ',-8002,'male',19,9176198010) * ERROR at line 1: ORA-02290: check constraint (SYSTEM.SYS_C004049) violated
- 17. (c) The agecannot be a null value. SQL> insert into stud_info values('REKA',8003,'female',20,7094182126); 1 row created. Verification for age cannot be a null value: SQL> insert into stud_info values('REKA',8003,'female',null,7094182126); insert into stud_info values('REKA',8003,'female',null,7094182126) * ERROR at line 1: ORA-01400: cannot insert NULL into ("SYSTEM"."STUD_INFO"."AGE")
- 18. (d) The gendermust be “Male” or “Female” or “Transgend” SQL> insert into stud_info values('THANSHIKA',8004,'female',21,8667512425); 1 row created. Verification for gender must be “Male” or “Female” or “Transgend”: SQL> insert into stud_info values('THANSHIKA',8004,'girl',21,8667512425); insert into stud_info values('THANSHIKA',8004,'girl',21,8667516626) * ERROR at line 1: ORA-02290: check constraint (SYSTEM.SYS_C004050) violated
- 19. (e) The mobilenumber may contain null values. SQL> insert into stud_info (name,roll_no,gender,age) values('NIRANJANA',8005,'female',18); 1 row created. SQL> select * from stud_info; NAME ROLL_NO gender AGE MOBILENO --------------------------- -------------- ----------- ------- ----------------- MATHAN 8001 male 20 6381761984 RAJ 8002 male 19 9176198010 REKA 8003 female 20 7094182126 THANSHIKA 8004 female 21 8667512425 NIRANJANA 8005 female 18
- 20. Ex. No: 4.Create a table student_master with the following attributes name, regno, dept and year of joining with suitable data types. Use Select command to do the following. (a) Display all the column in the student_ master table . (b) Display the student’s name column only. (c) Eliminate the duplicate entry in student_mastertable. (d) Select the details of student who is studying computer science department (e) Sort the attribute name in alphabetical order.
- 21. Create a tablestudent_master with the following attributes name, regno, dept and year of joining with suitable data types. SQL> create table student_master ( 2 name varchar(30), 3 reg_no number(10), 4 department char(10), 5 year_of_joining number(5) 6 ); Table created.
- 22. INSERT FEW RECORD: SQL>insert into student_master values('mathan',8001,'CSE',2022); 1 row created. SQL> insert into student_master values('arun',8002,'EEE',2023); 1 row created. SQL> insert into student_master values('sriram',8003,'ECE',2024); 1 row created. SQL> insert into student_master values('sakthi',8004,'EEE',2019); 1 row created. SQL> insert into student_master values('vasanth',8005,'ECE',2024); 1 row created. SQL> insert into student_master values('sakthi',8004,'EEE',2019); 1 row created.
- 23. (a) Display allthe column in the student_ master table. SQL> select * from student_master; NAME REG_NO DEPARTMENT YEAR_OF_JOINING ------------------------------ ---------- ------------------- ------------------------ mathan 8001 CSE 2022 arun 8002 EEE 2023 sriram 8003 ECE 2024 sakthi 8004 EEE 2019 vasanth 8005 ECE 2024 sakthi 8004 EEE 2019 6 rows selected.
- 24. (b) Display thestudent’s name column only. SQL> select name from student_master; NAME ------------------------------ mathan arun sriram sakthi vasanth sakthi 6 rows selected.
- 25. (c) Eliminate theduplicate entry in student_mastertable. SQL> select distinct * from student_master; NAME REG_NO DEPARTMENT YEAR_OF_JOINING ------------------------------ ---------- ----------------- -------------------------- sakthi 8004 EEE 2019 mathan 8001 CSE 2022 vasanth 8005 ECE 2024 sriram 8003 ECE 2024 arun 8002 EEE 2023
- 26. (d) Select thedetails of student who is studying computer science department. SQL> select * from student_master where department='CSE'; NAME REG_NO DEPARTMENT YEAR_OF_JOINING ---------------------------- ---------- ------------------ --------------- mathan 8001 CSE 2022
- 27. (e) Sort theattribute name in alphabetical order. SQL> select * from stud36 order by name asc; NAME REG_NO DEPARTMENT YEAR_OF_JOINING -------------------------- ---------- ----------------- ----------------------- arun 8002 EEE 2023 mathan 8001 CSE 2022 sakthi 8004 EEE 2019 sakthi 8004 EEE 2019 sriram 8003 ECE 2024 vasanth 8005 ECE 2024
- 28. Ex. No: 5.Create a table sales_order_details with the s_order_no as primary key and it contains the following fields: product_no, description, qty_ordered, qty_disp, product_rate, profit_percent, sell_price, supplier_name. Use Select command to do the following (a) Select each row and compute sell_price*.50 and sell_price*1.50 for each row selected. (b) Select product_no, profit_percent, Sell_price where profit_per is not between 10 and 20 both inclusive. (c) Select product_no, description, profit_percent, sell_price where profit_percent is not between 20 and 30. (d) Select the suppliername and product_no where suppliername has ‘r’ or ‘h’as second character.
- 29. Create a tablesales_order_details with the s_order_no as primary key and it contains the following fields: product_no, description, qty_ordered, qty_disp, product_rate, profit_percent, sell_price, supplier_name. Use Select command to do the following. SQL> create table sales_order_details ( 2 s_order_no number(10) primary key, 3 product_no number(10), 4 description char(20), 5 qty_orderd number(10), 6 qty_disp number(10), 7 product_rate number(10,2), 8 profit_percent number(5,2), 9 sell_price number(5,2), 10 supplier_name char(30) ); Table created.
- 30. INSERT FEW RECORDS. SQL>insert into sales_order_details values(1,101,'Product A',50,38,150,'',200,'sriram'); 1 row created. SQL> insert into sales_order_details values(2,102,'Product B',80,30,350,'',450,'arun'); 1 row created. SQL> insert into sales_order_details values(3,103,'Product C',130,70,100,'',125,'shana'); 1 row created. SQL> insert into sales_order_details values(4,104,'Product D',20,5,970.75,'',999,'arjun'); 1 row created. SQL> insert into sales_order_details values(5,105,'Product E',200,198,100,'',110,'roja'); 1 row created. SQL> insert into sales_order_details values(6,106,'Product F',34,20,100,'',115,'manoj'); 1 row created.
- 31. SQL> update sales_order_detailsset profit_percent=((sell_price-product_rate)/product_rate)*100 where s_order_no>0; 6 rows updated. Display the records: SQL> select * from sales_order_details; S_ORDER_NO PRODUCT_NO DESCRIPTION QTY_ORDERD QTY_DISP PRODUCT_RATE PROFIT_PERCENT SELL_PRICE SUPPLIER_NAME ------------------ -------------------- ------------------- ------------------ ------------ --------------------- ---------------------- --------------- ---------------------- 1 101 Product A 50 38 150 33.33 200 sriram 2 102 Product B 80 30 350 28.57 450 arun 3 103 Product C 130 70 100 25 125 shana 4 104 Product D 20 5 970.75 2.91 999 arjun 5 105 Product E 200 198 100 10 110 roja 6 106 Product F 34 20 100 15 115 manoj
- 32. (a) Select eachrow and compute sell_price*.50 and sell_price*1.50 for each row selected. SQL> select sell_price,sell_price*0.50,sell_price*1.50 from sales_order_details; SELL_PRICE SELL_PRICE*0.50 SELL_PRICE*1.50 --------------- ---------------------- ---------------------- 200 100 300 450 225 675 125 62.5 187.5 999 499.5 1498.5 110 55 165 115 57.5 172.5
- 33. (b) Select product_no,profit_percent, Sell_price where profit_per is not between 10 and 20 both inclusive. SQL> select product_no,profit_percent,sell_price from sales_order_details where profit_percent not between 10 and 20; PRODUCT_NO PROFIT_PERCENT SELL_PRICE ------------------- ----------------------- --------------- 101 33.33 200 102 28.57 450 103 25 125 104 2.91 999
- 34. (c) Select product_no,description, profit_percent, sell_price where profit_percent is not between 20 and 30. SQL> select product_no,description,profit_percent,sell_price from sales_order_details where profit_percent not between 20 and 30; PRODUCT_NO DESCRIPTION PROFIT_PERCENT SELL_PRICE ------------------- ------------------ ----------------------- -------------- 101 Product A 33.33 200 104 Product D 2.91 999 105 Product E 10 110 106 Product F 15 115
- 35. (d) Select thesuppliername and product_no where suppliername has ‘r’ or ‘h’as second character. SQL> select supplier_name,product_no from sales_order_details where substr(supplier_name,2,1)='r' or substr(supplier_name,2,1)='h'; SUPPLIER_NAME PRODUCT_NO ----------------------------- ------------------- sriram 101 arun 102 shana 103 arjun 104
- 36. Ex. No: 6.Create an Employee table with the following attributes: employee_number, name, job and manager_id. Set the manager_id as a foreign key for creating self referential structure. (a) Insert few records (b) Display all the records (c) Display the employee details who are working under particular manager_id.
- 37. Create an Employeetable with the following attributes: employee_number, name, job and manager_id. Set the manager_id as a foreign key for creating self referential structure. SQL> create table emp ( 2 employee_number number(10) primary key, 3 name varchar(30) not null, 4 job char(50), 5 manager_id number(10), 6 foreign key (manager_id) references emp(employee_number) 7 ); Table created.
- 38. (a) Insert fewrecords. SQL> insert into emp values(7329,'smith','CEO',7329); 1 row created. SQL> insert into emp values(7499,'allen','VP-SALES','7329'); 1 row created. SQL> insert into emp values(7521,'ward','MANAGER','7499'); 1 row created. SQL> insert into emp values(7566,'jones','SALES MAN','7521'); 1 row created. SQL> insert into emp values(7923,'david','DEVELOPER','7566'); 1 row created.
- 39. Verification for foreignkey: SQL> insert into emp values(7571,'ford','MANAGER','7331'); insert into emp values(7571,'ford','MANAGER','7331') * ERROR at line 1: ORA-02291: integrity constraint (SYSTEM.SYS_C004070) violated - parent key not found (b) Display all the records SQL> select * from emp; EMPLOYEE_NUMBER NAME JOB MANAGER_ID ----------------------------- ---------- --------------- ------------------ 7329 smith CEO 7329 7499 allen VP-SALES 7329 7521 ward MANAGER 7499 7566 jones SALES MAN 7521 7923 david DEVELOPER 7566
- 40. (c) Display theemployee details who are working under particular manager_id. SQL> select * from emp where manager_id=7329; EMPLOYEE_NUMBER NAME JOB MANAGER_ID ----------------------------- -------- ---------- -------------------- 7329 smith CEO 7329 7499 allen VP-SALES 7329
- 41. Ex. No: 7.Create an Employee table with the following attributes: employee_number, employee_name, department_number, job and salary. (a) Query to display the employee_name and Salary of all the employees earning more than 20000 INR. (b) Query to display employee_name and department_number for the particular employee _number. (c) Query to display employee_name and Salary for all employees whose salary is not in the range of INR 15000 and INR 30000.
- 42. Create an Employeetable with the following attributes: employee_number, employee_name, department_number, job and salary. SQL> create table emp2 ( 2 emp_no number(5), 3 emp_name char(30), 4 department_no number(10), 5 job char(30), 6 salary number(20) ); Table created.
- 43. INSERT FEW RECORDS: SQL>insert into emp2 values(1102,'anbu',128,'developer',50000); 1 row created. SQL> insert into emp2 values(1103,'prakash',131,'soft-Engineer',30000); 1 row created. SQL> insert into emp2 values(1104,'raman',111,'designer',10000); 1 row created. SQL> insert into emp2 values(1105,'hari',132,'team leader',16000); 1 row created. SQL> insert into emp2 values(1106,'prasanth',122,'tester',18000); 1 row created.
- 44. (a) Query todisplay the employee_name and Salary of all the employees earning more than 20000 INR. SQL> select emp_name,salary from emp2 where salary>20000; EMP_NAME SALARY ------------------------------ ---------- anbu 50000 prakash 30000
- 45. (b) Query todisplay employee_name and department_number for the particular employee _number. SQL> select emp_name,department_no from emp2 where emp_no=1103; EMP_NAME DEPARTMENT_NO ------------------------------ ----------------------- prakash 131
- 46. (c) Query todisplay employee_name and Salary for all employees whose salary is not in the range of INR 15000 and INR 30000. SQL> select emp_name,salary from emp2 where salary<15000 or salary >30000; (OR) SQL> select emp_name,salary from emp2 where salary not between 15000 and 30000; EMP_NAME SALARY ------------------------------ ------------ anbu 50000 raman 10000
- 47. Ex. No: 8.Create an Employee table with the following attribute employee_number, employee_name, job_type, hire_date, department_number and salary. (a) Query to display employee_name and department_number of all the employees in department_number 10 and Department number 20 in the alphabetical order by name. (b) Query to display Name of all the employees where the third letter of their name is =A. (c) Query to display Name with the 1st letter capitalized and all other letter lowercase (d) Query to display Name of all employees either have two R‘s or have two A‘s in their Name.
- 48. Create an Employeetable with the following attribute employee_number, employee_name, job_type, hire_date, department_number and salary. SQL> create table employee ( 2 emp_no number(10), 3 emp_name char(30), 4 job_type char(30), 5 hire_date date, 6 department_no number(10), 7 salary number(10) ); Table created.
- 49. INSERT A RECORD SQL>insert into employee values(1,'MATHAN','CEO','22-feb-22',10,80000); 1 row created. SQL> insert into employee values(2,'PRATHAP','manager','18-aug-14',10,100000); 1 row created. SQL> insert into employee values(3,'SRIRAM','sales-man','13-jan-21',8,40000); 1 row created. SQL> insert into employee values(4,'CHARU','designer','24-may-20',20,40000); 1 row created. SQL> insert into employee values(5,'MATHI','manager','13-jan-19',7,50000); 1 row created.
- 50. (a) Query todisplay employee_name and department_number of all the employees in department_number 10 and Department number 20 in the alphabetical order by name. SQL> select emp_name,department_no from employee where department_no=10 or department_no=20 order by emp_name asc; EMP_NAME DEPARTMENT_NO ------------------------------ ------------------------- CHARU 20 MATHAN 10 PRATHAP 10
- 51. (b) Query todisplay Name of all the employees where the third letter of their name is =A. SQL> select emp_name from employee where substr(emp_name,3,1)='A'; EMP_NAME ------------------------------ PRATHAP CHARU
- 52. (c) Query todisplay Name with the 1st letter capitalized and all other letter lowercase SQL> select initcap(emp_name) from employee; INITCAP(EMP_NAME) ------------------------------ Mathan Prathap Sriram Charu Mathi
- 53. (d) Query todisplay Name of all employees either have two R‘s or have two A‘s in their Name. SQL> select emp_name from employee where emp_name like '%R%R%' or emp_name like '%A%A%'; EMP_NAME ------------------------------ MATHAN PRATHAP SRIRAM
- 54. Ex. No: 9.Create an Employee table with the following attributes: employee_number, name, job, hire_date and manager_id. Set the manager_id as a forein key for creating selfereferential structure. (a) Query to display name and Hire Date of every Employee who was hired in 2007. (b) Query to display name and calculate the number of months between today and the date each employee was hired. (c) Query to display name and job of all employees who don‘t have a current Manager.
- 55. Create an Employeetable with the following attributes: employee_number, name, job, hire_date and manager_id. Set the manager_id as a forein key for creating selfereferential structure. SQL> create table employee1 ( 2 emp_no number(10) primary key, 3 emp_name char(20), 4 job char(20), 5 hire_date date, 6 manager_id number(10), 7 foreign key(manager_id) references employee1(emp_no) ); Table created.
- 56. INSERT FEW RECORDS: SQL>insert into employee1 values(7345,'prathap','CEO','12-aug-2007',NULL); 1 row created. SQL> insert into employee1 values(7356,'kannan','manager','17-jan-2010',7345); 1 row created. SQL> insert into employee1 values(7358,'surya','designer','18-feb-2011',7356); 1 row created. SQL> insert into employee1 values(7353,'sriram','manger','22-may-2007',NULL); 1 row created. SQL> insert into employee1 values(7378,'jothika','sales','15-jul-2015',7353); 1 row created.
- 57. Display the records: SQL>select * from employee1; EMP_NO EMP_NAME JOB HIRE_DATE MANAGER_ID ------------ -------------------- ------------- ---------------- -------------------- 7345 prathap CEO 12-AUG-07 7356 kannan manager 17-JAN-10 7345 7358 surya designer 18-FEB-11 7356 7353 sriram manager 22-MAY-07 7378 jothika sales 15-JUL-15 7353
- 58. (a) Query todisplay name and Hire Date of every Employee who was hired in 2007. SQL> select emp_name,hire_date from employee1 where extract(year from hire_date)=2007; EMP_NAME HIRE_DATE -------------------- ------------------------ prathap 12-AUG-07 sriram 22-MAY-07
- 59. (b) Query todisplay name and calculate the number of months between today and the date each employee was hired. SQL> select emp_name,round(months_between(sysdate,hire_date)) as months_since_hired from employee1; EMP_NAME MONTHS_SINCE_HIRED -------------------- ------------------------------- prathap 203 kannan 174 surya 161 sriram 206 jothika 108
- 60. (c) Query todisplay name and job of all employees who don‘t have a current Manager. SQL> select emp_name,job from employee1 where manager_id is NULL; EMP_NAME JOB -------------------- -------------------- prathap CEO sriram tester