API tools faq
paste
GamerBhai02

DBMS All Exp

GamerBhai02
May 16th, 2025
61
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 16.53 KB | Source Code | 0 0
raw download clone embed print report
  1. https://g.co/gemini/share/064c1e56fe34
  2.  
  3. show databases;
  4. create database experiment;
  5. use experiment;
  6. create table student(ID int,Name varchar(30),Age int);
  7. alter table student add Grade varchar(3);
  8. desc student;
  9. insert into student (ID,Name,Age,Grade) values(101,'John Doe',25,'A'),(201,'Willliam Turber',30,'O'),(301,'Olivia',19,'B'),(401,'Alice Brown',21,'A'),(501,'Sheldon Cooper',20,'S');
  10. select * from student;
  11. set sql_safe_updates=0;
  12. update student set  age=21 where ID=101;
  13. delete from student where id=101;
  14. select * from student;
  15. select * from student where age>=19;
  16. select * from student where Name='Willliam Turber' or Name='Alice Brown';
  17. select * from student where Name in ('Willliam Turber','Alice Brown');
  18. select * from student;
  19. select * from student where Grade in ('A','B') and age>=20;
  20. select * from student where Age between 20 and 25;
  21. select * from student where Age<20;
  22. select * from student where Grade>'B';
  23.  
  24. create table employee(id int,firstname varchar(100),lastname varchar(100),address varchar(100),city varchar(100));
  25. insert into employee(id,firstname,lastname,address,city) values(101,'John','Kumar','Lane 1','Bengaluru');
  26. insert into employee(id,firstname,lastname,address,city) values(102,'King','Singh','Lane 2','Bengaluru');
  27. insert into employee(id,firstname,lastname,address,city) values(103,'Ben','Sharma','Lane 3','Bengaluru');
  28. select * from employee;
  29. alter table employee add column salary int;
  30. select * from employee;
  31. set sql_safe_updates = 0;
  32. update employee set salary = case when id=101 then 10000 when id=102 then 20000 when id=103 then 30000 else salary end;
  33. select * from employee;
  34. alter table employee drop column salary;
  35. select * from employee;
  36. alter table employee rename column city to jagah;
  37. select * from employee;
  38. rename table employee to gulaam;
  39. select * from gulaam;
  40.  
  41. create table student(snum integer,sname varchar(10),major varchar(2),level varchar(2),age integer,PRIMARY KEY(snum));
  42. create table faculty(fid integer,fname varchar(20),deptid integer,PRIMARY KEY(fid));
  43. create table class(cname varchar(20),meets_at varchar(10),room varchar(10),fid integer,PRIMARY KEY(cname),FOREIGN KEY(fid) REFERENCES faculty(fid));
  44. create table enrolled(snum integer,cname varchar(20),PRIMARY KEY(snum,cname),FOREIGN KEY(snum) REFERENCES student(snum),FOREIGN KEY(cname) REFERENCES class(cname));
  45. insert into student(snum,sname,major,level,age) values
  46. (1,'jhon','CS','Sr',19),(2,'smith','CS','Jr',20),(3,'jacob','CV','Sr',20),(4,'tom','CS','Jr',20),(5,'sid','CS','Jr',20),(6,'harry','CS','Sr',21);
  47. insert into faculty(fid,fname,deptid) values(11,'Harshith',1000),(12,'Mohan',1000),(13,'Kumar',1001),(14,'Shobha',1002),(15,'Shan',1000);
  48. insert into student(snum,sname,major,level,age) values(7,'Kala','AI','Sr',21);
  49. insert into class(cname,meets_at,room,fid) values
  50. ('class1','noon','room1',14),('class10','morning','room128',14),
  51. ('class2','morning','room2',12),('class3','morning','room3',11),
  52. ('class4','evening','room4',14),('class5','night','room3',15),
  53. ('class6','morning','room2',14),('class7','morning','room3',14);
  54. insert into enrolled(snum,cname) values
  55. (1,'class1'),(2,'class1'),(3,'class3'),(4,'class3'),(3,'class4'),(5,'class4'),
  56. (1,'class5'),(2,'class5'),(3,'class5'),(4,'class5'),(5,'class5'),(6,'class5');
  57. select * from student;
  58. select * from class;
  59. select * from enrolled;
  60. select * from faculty;
  61. select distinct s.sname from student s,class c,faculty f,enrolled e where s.snum=e.snum and e.cname=c.cname and s.level='Jr' and f.fname='Harshith' and f.fid=c.fid;
  62. select distinct cname from class where room='room128' or cname in (select e.cname from enrolled e group by e.cname having COUNT(*)>=5);
  63. INSERT INTO enrolled (snum,cname) VALUES (3,'class2');
  64. SELECT DISTINCT s.sname FROM student s WHERE s.snum IN (SELECT el.snum FROM enrolled el,enrolled e2,class c1,class c2 WHERE el.snum=e2.snum AND el.cname=c1.cname AND e2.cname=c2.cname AND c1.meets_at=c2.meets_at);
  65. select f.fname, f.fid from faculty f where f.fid in (select fid from class group by fid having count(*)=(select count(distinct room) from class));
  66. select f.fname from faculty f WHERE 5>(select count(e.snum) from class c, enrolled e where c.cname=e.cname and c.fid=f.fid);
  67.  
  68. create table flight(flno integer,frm varchar(20),too varchar(20),distance integer,departs varchar(20),arrives varchar(20),price integer,PRIMARY KEY(flno));
  69. create table aircraft(aid integer,aname varchar(20),cruisingrange integer,PRIMARY KEY(aid));
  70. create table employees(eid integer,ename varchar(20),salary integer,PRIMARY KEY(eid));
  71. create table certified(eid integer,aid integer,PRIMARY KEY(eid,aid),FOREIGN KEY(eid) REFERENCES employees(eid),FOREIGN KEY(aid) REFERENCES aircraft(aid));
  72. insert into flight(flno, frm, too, distance, departs, arrives,price) values
  73. (1, 'Bangalore', 'Mangalore', 360, '10:45:00', '12:00:00',10000),
  74. (2,'Bangalore', 'Delhi', 5000,'12:15:00','04:30:00',25000),
  75. (3,'Bangalore', 'Mumbai', 3500,'02:15:00','05:25:00',30000),
  76. (4,'Delhi', 'Mumbai', 4500,'10:15:00', '12:05:00',35000),
  77. (5,'Delhi', 'Frankfurt', 18000,'07:15:00','05:30:00',90000),
  78. (6,'Bangalore', 'Frankfurt', 19500,'10:00:00','07:45:00',95000),
  79. (7,'Bangalore', 'Frankfurt', 17000,'12:00:00','06:30:00',99000);
  80. insert into aircraft(aid,aname,cruisingrange) values
  81. (123,'Airbus', 1000),(302,'Boeing', 5000),(306,'Jet01',5000),(378,'Airbus380',8000),
  82. (456,'Aircraft',500),(789,'Aircraft02',800),(951,'Aircraft03',1000);
  83. insert into employees(eid,ename,salary) values
  84. (1,'Ajay',30000),(2,'Ajith',85000),(3,'Arnab', 50000),(4,'Harry', 45000),
  85. (5,'Ron',90000),(6,'Josh', 75000),(7,'Ram', 100000);
  86. insert into certified(eid,aid) values
  87. (1,123),(2,123),(1,302),(5,302),(7,302),(1,306),(2,306),(1,378),(2,378),(4,378),(6,456),(3,456),(5,789),(6,789),(3,951),(1,951),(1,789);
  88. select * from employees;
  89. select * from aircraft;
  90. select * from flight;
  91. select * from certified;
  92. select distinct a.aname from aircraft a, certified c, employees e where a.aid=c.aid and c.eid=e.eid and e.salary>80000;
  93. SELECT DISTINCT a.aname FROM aircraft a, certified c, employees e WHERE a.aid=c.aid AND c.eid=e.eid AND NOT EXISTS (SELECT * FROM employees e1 WHERE e1.eid=e.eid AND e1.salary<80000);
  94. SELECT c.eid,MAX(cruisingrange) FROM certified c,aircraft a WHERE c.aid=a.aid GROUP BY c.eid HAVING COUNT(*)>3;
  95. SELECT DISTINCT e.ename FROM employees e WHERE e.salary< (SELECT MIN(f.price) FROM flight f WHERE f.frm='Bangalore' AND f.too='Frankfurt');
  96. SELECT a.aname, AVG(e.salary) FROM aircraft a,certified c,employees e WHERE a.aid=c.aid AND c.eid=e.eid AND a.cruisingrange>1000 GROUP BY a.aname;
  97. SELECT distinct e.ename FROM employees e,aircraft a,certified c WHERE e.eid=c.eid AND c.aid=a.aid AND a.aname='Boeing';
  98. SELECT a.aid FROM aircraft a WHERE a.cruisingrange>(SELECT MIN(f.distance) FROM flight f WHERE f.frm='Bangalore' AND f.too='Delhi');
  99.  
  100. create table customer(cust int,cname varchar(10), city varchar(10), primary key(cust));
  101. create table order1(ord int, odate date,cust int,ord_amt int,primary key(ord));
  102. create table item(item int, unit_price int, primary key(item));
  103. create table order_item(ord int REFERENCES order1(ord), item int REFERENCES item(item) on delete set null, qty int);
  104. create table warehouse(warehouse int, city varchar(15), primary key(warehouse));
  105. create table shipment(ord int REFERENCES order1(ord), warehouse int REFERENCES warehouse(warehouse), ship_date date);
  106. insert into customer(cust,cname,city) values
  107. (2,'shashi','N.America'),(3,'kala','U.S.A'),(5,'Dumini','India'),(2002,'Stuart','Switz'),
  108. (2003,'Binny','N.America'),(2004,'Curie','U.S.A'),(2005,'Dumini','India'),(2006,'Rick','India');
  109. insert into item(item,unit_price) values
  110. (1500,250),(750,167),(125,124),(136,420),(250,138),(10,500);
  111. insert into shipment(ord,warehouse,ship_date) values
  112. (1,322,'4-03-21'),(2,234,'7-12-23'),(3,324,'14-06-24'),
  113. (4,325,'12-10-25'),(5,326,'12-4-25');
  114. insert into order1(ord,odate,cust,ord_amt) values
  115. (1,'2006-10-5',2,5000),(2,'2006-10-8',3,2500),(3,'2006-10-9',3,1000),
  116. (4,'2006-10-6',5,1000),(5,'2006-11-3',1,5000);
  117. insert into order_item(ord,item,qty) values
  118. (1,1500,1),(1,750,1),(2,125,1),(3,136,5),(4,250,2),(4,10,2);
  119. insert into warehouse(warehouse,city) values
  120. (321,'Bangalore'),(322,'Hyderabad'),(323,'Bangalore'),(324,'New Delhi'),(325,'New York');
  121. select c.cname, count(*) as no_of_orders, avg(o.ord_amt) as avg_order_amt from customer c,order1 o where c.cust=o.cust group by c.cname;
  122. select ord from warehouse w,shipment s where w.warehouse=s.warehouse and city="New Delhi";
  123. delete from item where item=250;
  124. select * from order_item;
  125.  
  126. Create table author (
  127.     author_id int primary key,
  128.     name varchar(50),
  129.     city varchar(50),
  130.     country varchar(50)
  131. );
  132. Create table publisher (
  133.     publisher_id int primary key,
  134.     name varchar(50),
  135.     city varchar(50),
  136.     country varchar(50)
  137. );
  138. Create table category (
  139.     category_id int primary key,
  140.     description varchar(100)
  141. );
  142. Create table catalog (
  143.     book_id int primary key,
  144.     title varchar(100),
  145.     author_id int,
  146.     publisher_id int,
  147.     category_id int,
  148.     year int,
  149.     price int,
  150.     foreign key (author_id) references author(author_id),
  151.     foreign key (publisher_id) references publisher(publisher_id),
  152.     foreign key (category_id) references category(category_id)
  153. );
  154. Create table order_details (
  155.     order_no int,
  156.     book_id int,
  157.     quantity int,
  158.     primary key (order_no, book_id),
  159.     foreign key (book_id) references catalog(book_id)
  160. );
  161. insert into author (author_id, name, city, country) values
  162. (1, 'Author A', 'City 1', 'Country 1'),
  163. (2, 'Author B', 'City 2', 'Country 2'),
  164. (3, 'Author C', 'City 3', 'Country 3'),
  165. (4, 'Author D', 'City 4', 'Country 4'),
  166. (5, 'Author E', 'City 5', 'Country 5');
  167. insert into publisher (publisher_id, name, city, country) values
  168. (1, 'Publisher A', 'City 1', 'Country 1'),
  169. (2, 'Publisher B', 'City 2', 'Country 2'),
  170. (3, 'Publisher C', 'City 3', 'Country 3'),
  171. (4, 'Publisher D', 'City 4', 'Country 4'),
  172. (5, 'Publisher E', 'City 5', 'Country 5');
  173. insert into category (category_id, description) values
  174. (1, 'Fiction'),(2, 'Non-Fiction'),(3, 'Science Fiction'),
  175. (4, 'Mystery'),(5, 'Thriller');
  176. insert into catalog (book_id, title, author_id, publisher_id, category_id, year, price) values
  177. (1, 'Book 1', 1, 1, 1, 2005, 100),(2, 'Book 2', 2, 1, 2, 2010, 120),
  178. (3, 'Book 3', 3, 3, 3, 2015, 150),(4, 'Book 4', 4, 4, 4, 2020, 200),
  179. (5, 'Book 5', 5, 5, 5, 2025, 250);
  180. insert into order_details (order_no, book_id, quantity)
  181. values (101, 1, 2),(102, 2, 3),
  182. (103, 3, 1),(104, 4, 4),(105, 5, 2);
  183. select a.author_id, a.name, a.city, a.country, count(c.book_id) as book_count from author a
  184. join catalog c on a.author_id = c.author_id where c.year > 2000
  185. group by a.author_id, a.name, a.city, a.country having count(c.book_id) >= 2;
  186. select a.author_id, a.name, a.city, a.country, c.title, od.quantity from order_details od
  187. join catalog c on od.book_id = c.book_id join author a on c.author_id = a.author_id
  188. where od.quantity = (select max(quantity) from order_details);
  189. update catalog set price = price * 1.10 where
  190. publisher_id = (select publisher_id from publisher where name = 'Publisher E');
  191. select * from catalog;
  192.  
  193. create table student (regno varchar(15), name varchar(20), major varchar(20), bdate date, primary key (regno));
  194. insert into student (regno, name, major, bdate) values
  195. ('Inh21ai001', 'a', 'sr', '19930120'),('Inh21ai002', 'b', 'sr', '19930924'),
  196. ('Inh21ai003', 'c', 'sr', '19931127'),('Inh21ai004', 'd', 'sr', '19930413'),('Inh21ai005', 'e', 'isr', '19940824');
  197. create table course (courseno int, cname varchar(20), dept varchar(20), primary key (courseno));
  198. insert into course values (111, 'DS', 'CSE'),(112, 'EC', 'CSE'),
  199. (113, 'SS', 'ISE'),(114, 'DBMS', 'CSE'),(115, 'SSANNIS', 'ECE'),
  200. (121, 'OS', 'AIML'),(123, 'SS', 'AIML'),(124, 'DBMS', 'AIML');
  201. create table text (book_isbn varchar(20), book_title varchar(20), publisher varchar(20), author varchar(20), primary key (book_isbn));
  202. insert into text (book_isbn, book_title, publisher, author) values
  203. ('900', 'Database Systems', 'Pearson', 'Schield'),
  204. ('901', 'Operating Sys', 'Pearson', 'Leland'),
  205. ('902', 'Circuits', 'Hall India', 'Bob'),
  206. ('903', 'System Software', 'Peterson', 'Jacob'),
  207. ('904', 'Scheduling', 'Pearson', 'Patil'),
  208. ('905', 'Database Systems', 'Pearson', 'Jacob'),
  209. ('906', 'Database Manager', 'Pearson', 'Bob'),
  210. ('907', 'Signali', 'Hall India', 'Sumit');
  211. create table enroll (regno varchar(15), courseno int, sem int, marks int, primary key (regno, courseno), foreign key (regno) references student (regno), foreign key (courseno) references course (courseno));
  212. insert into enroll (regno, courseno, sem, marks) values
  213. ('Inh21ai001', 114, 3, 100),('Inh21ai002', 115, 3, 100),
  214. ('Inh21ai003', 113, 5, 100),('Inh21ai004', 111, 5, 100),('Inh21ai005', 112, 3, 100);
  215. create table book_adoption (courseno int, sem int(3), book_isbn varchar(20), primary key (courseno, sem, book_isbn), foreign key (courseno) references course (courseno), foreign key (book_isbn) references text (book_isbn));
  216. insert into book_adoption (courseno, sem, book_isbn) values
  217. (111, 5, '900'),(111, 3, '903'),(114, 5, '904'),(113, 5, '902'),
  218. (115, 3, '906'),(121, 5, '903'),(121, 5, '904'),(121, 5, '905');
  219. select c.courseno, t.book_isbn, t.book_title from course c, book_adoption ba, text t where c.courseno = ba.courseno and ba.book_isbn = t.book_isbn and c.dept = 'AIML' and 2 <= (select count(book_isbn) from book_adoption b where c.courseno = b.courseno) order by t.book_title;
  220. select distinct c.dept from course c where c.dept in (select c.dept from course c, book_adoption b, text t where c.courseno = b.courseno and b.book_isbn = t.book_isbn and t.publisher = 'Pearson') and c.dept not in (select c.dept from course c, book_adoption b, text t where c.courseno = b.courseno and b.book_isbn = t.book_isbn and t.publisher != 'Pearson');
  221.  
  222. create table actor (act_id integer primary key, act_name varchar(20), act_gender char(1));
  223. create table director (dir_id integer primary key, dir_name varchar(20), dir_phone varchar(20));
  224. create table movies (mov_id integer primary key, mov_title varchar(25), mov_year integer, mov_lang varchar(15), dir_id integer, foreign key (dir_id) references director (dir_id));
  225. create table movie_cast (act_id integer, mov_id integer, role varchar(10), primary key (act_id, mov_id), foreign key (act_id) references actor (act_id), foreign key (mov_id) references movies (mov_id));
  226. create table rating (mov_id integer primary key, rev_stars varchar(25), foreign key (mov_id) references movies (mov_id));
  227. insert into actor (act_id, act_name, act_gender) values
  228. (101, 'Rahul', 'M'),(102, 'Ankitha', 'F'),(103, 'Radhika', 'F'),
  229. (104, 'Chethan', 'M'),(105, 'Vivan', 'M');
  230. insert into director (dir_id, dir_name, dir_phone) values
  231. (201, 'Anoop', '9181818181'),(202, 'Hitchcock', '9181818182'),
  232. (203, 'Shashank', '9181818183'),(204, 'Steven Spielberg', '9181818184'),
  233. (205, 'Anand', '9181818185');
  234. insert into movies (mov_id, mov_title, mov_year, mov_lang, dir_id) values
  235. (1001, 'MANASU', 2017, 'Kannada', 201),(1002, 'AAKASHAM', 2015, 'Telugu', 202),
  236. (1003, 'KALIYONA', 2008, 'Kannada', 201),(1004, 'WAR HORSE', 2011, 'English', 204),
  237. (1005, 'HOME', 2012, 'English', 205);
  238. insert into movie_cast (act_id, mov_id, role) values
  239. (101, 1002, 'Hero'),(101, 1001, 'Hero'),(103, 1003, 'Heroine'),
  240. (103, 1002, 'Count'),(104, 1004, 'Hero'),(105, 1005, 'Hero');
  241. insert into rating (mov_id, rev_stars) values
  242. (1001, 4),(1002, 3),(1003, 5),(1004, 4),(1005, 3);
  243. select mov_title from movies where dir_id = (select dir_id from director where dir_name = 'Hitchcock');
  244. select m.mov_title from movies m join movie_cast mc on m.mov_id = mc.mov_id group by m.mov_id having count(mc.act_id) >= 2;
  245. select act_name from actor a join movie_cast mc on a.act_id = mc.act_id join movies m on mc.mov_id = m.mov_id where m.mov_year not between 2000 and 2015;
  246. select m.mov_title, max(r.rev_stars) from movies m inner join rating r using (mov_id) group by m.mov_title having max(r.rev_stars) > 0 order by m.mov_title;
  247. update rating set rev_stars = 5 where mov_id in (select mov_id from movies where dir_id in (select dir_id from director where dir_name = 'Steven Spielberg'));
  248. select * from rating;
  249.  
  250. create table employee(e_id int primary key,e_name varchar(50),age int,salary int);
  251. insert into employee(e_id, e_name, age, salary) values
  252. (1, 'Ani', 30, 50000),
  253. (2, 'Bob', 25, 60000),
  254. (3, 'Charlie', 35, 55000),
  255. (4, 'David', 28, 50000),
  256. (5, 'Eva', 30, 70000);
  257. select count(e_name) as Total_Employees from employee;
  258. select max(age) as Max_Age from employee;
  259. select min(age) as Min_Age from employee;
  260. select e_name, salary from employee order by salary asc;
  261. select salary, count(*) as Num_Of_Employees from employee group by salary;
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!