/////order
create database order_new;
use order_new;
create table salesman(sale_id int primary key,name varchar(10),city varchar(10),comission varchar(10));
create table customer(cust_id int primary key,cust_name varchar(10),city varchar(10),grades int,sale_id int,foreign key(sale_id) references salesman(sale_id) on delete cascade);
create table orders(order_no int primary key,purchase_amount int ,order_date date,cust_id int,sale_id int,foreign key(cust_id) references customer(cust_id) on delete cascade);
alter table orders add foreign key(sale_id) references salesman(sale_id) on delete cascade;
insert into salesman values(1000,”Rahul”,”Bangalore”,”10%”);
insert into salesman values(1001,”Raj”,”Bangalore”,”10%”);
insert into salesman values(1002,”Tom”,”Patna”,”20%”);
insert into salesman values(1003,”Sahil”,”Ranchi”,”30%”);
insert into salesman values(1004,”Ram”,”Bangalore”,”40%”);
insert into customer values(100,”Honey”,”Bangalore”,100,1000);
insert into customer values(101,”Kumar”,”Gaziabad”,400,1000);
insert into customer values(102,”Ronaldo”,”Bangalore”,100,1003);
insert into customer values(103,”Cs4″,”Ranchi”,300,1001);
insert into customer values(104,”Shyam”,”Bangalore”,100,1004);
insert into customer values(105,”Shyam”,”Jaipur”,950,1000);
insert into orders values(100,”1000″,”2019-09-02″,100,1000);
insert into orders values(101,”2000″,”2019-09-02″,101,1000);
insert into orders values(102,”2500″,”2019-09-02″,102,1003);
insert into orders values(103,”3000″,”2019-09-02″,100,1001);
insert into orders values(104,”4000″,”2019-09-02″,104,1000);
SELECT grades, COUNT(cust_id)
FROM customer
GROUP BY grades
HAVING grades > (SELECT avg(grades)
FROM customer
WHERE city=”Bangalore”);
SELECT sale_id, name
FROM salesman A
WHERE 1 < (SELECT COUNT(*)
FROM customer
WHERE sale_id=A.sale_id);
SELECT salesman.sale_id, name, cust_name, comission
FROM salesman, customer
WHERE salesman.city = customer.city
UNION
SELECT sale_id, name, ‘NO MATCH’, comission
FROM salesman
WHERE NOT city = ANY
(SELECT city
FROM customer)
ORDER BY 2 DESC;
CREATE VIEW t as
SELECT B.order_date, A.sale_id, A.name
FROM salesman A, orders B
WHERE A.sale_id = B.sale_id
AND B.purchase_amount=(SELECT MAX(purchase_amount)
FROM orders C
WHERE C.order_date = B.order_date);
SELECT * FROM t;
DELETE FROM salesman
WHERE sale_id=1001;
//movie
create database movie_dum;
use movie_dum;
create table actor(actor_id int primary key,act_name varchar(10),gender varchar(10));
create table director(dir_id int primary key,dir_name varchar(10),dir_phone varchar(10));
create table movies(movie_id int primary key,mov_title varchar(10),mov_year int,mov_lan varchar(10),dir_id int,foreign key(dir_id) references director(dir_id)on delete cascade);
create table movie_cast(actor_id int ,movie_id int ,role varchar(10),primary key(actor_id,movie_id),foreign key(actor_id) references actor(actor_id) on delete cascade);
alter table movie_cast add foreign key(movie_id) references movies(movie_id);
create table rating(movie_id int primary key,rev_stars varchar(10),foreign key(movie_id) references movies(movie_id) on delete cascade);
INSERT INTO actor VALUES (301,”ANUSHKA”,”F”);
INSERT INTO actor VALUES (302,”PRABHAS”,”M”);
INSERT INTO actor VALUES (303,”UNITH”,”M”);
INSERT INTO actor VALUES (304,”JERMY”,”M”);
INSERT INTO director VALUES (60,”RAJAMOULI”, 8751611001);
INSERT INTO director VALUES (61,”HITCHCOCK”, 7766138911);
INSERT INTO director VALUES (62,”FARAN”, 9986776531);
INSERT INTO director VALUES (63,”STEVENG”, 8989776530);
INSERT INTO movies VALUES (1001,”BAHUBALI-2″, 2017, “TELAGU”, 60);
INSERT INTO movies VALUES (1002,”BAHUBALI-1″, 2015, “TELAGU”, 60);
INSERT INTO movies VALUES (1003,”AKASH”, 2008, “KANNADA”, 61);
INSERT INTO movies VALUES (1004,”WAR HORSE”, 2011, “ENGLISH”, 63);
INSERT INTO movie_cast VALUES (301, 1002, “HEROINE”);
INSERT INTO movie_cast VALUES (301, 1001, “HEROINE”);
INSERT INTO movie_cast VALUES (303, 1003, “HERO”);
INSERT INTO movie_cast VALUES (303, 1002, “GUEST”);
INSERT INTO movie_cast VALUES (304, 1004, “HERO”);
INSERT INTO rating VALUES (1001, 4);
INSERT INTO rating VALUES (1002, 2);
INSERT INTO rating VALUES (1003, 5);
INSERT INTO rating VALUES (1004, 4);
SELECT mov_title
FROM movies
WHERE dir_id IN (SELECT dir_id
FROM director
WHERE dir_name = “HITCHCOCK”);
SELECT mov_title
FROM movies M, movie_cast MV
WHERE M.movie_id = MV.movie_id
AND actor_id IN (
SELECT actor_id
FROM movie_cast
GROUP BY actor_id
HAVING COUNT(actor_id) > 1
)
GROUP BY mov_title
HAVING COUNT(*) > 1;
SELECT act_name, mov_title, mov_year
FROM actor A
JOIN movie_cast C
ON A.actor_id=C.actor_id
JOIN movies M
ON C.movie_id=M.movie_id
WHERE M.mov_year NOT BETWEEN 2000 AND 2015;
SELECT mov_title, MAX(rev_stars)
FROM movies m
INNER JOIN RATING r on r.movie_id=m.movie_id
GROUP BY mov_title
HAVING MAX(rev_stars)>0
ORDER BY mov_title;
UPDATE rating
SET rev_stars = 5
WHERE movie_id IN (
SELECT movie_id
FROM movies
WHERE dir_id IN (
SELECT dir_id
FROM director
WHERE dir_name = “STEVENG”
)
);
select * from rating;
//college
create database college_new;
use college_new;
CREATE TABLE STUDENT (
USN VARCHAR (10) PRIMARY KEY,
SNAME VARCHAR (25),
ADDRESS VARCHAR (25),
PHONE int,
GENDER CHAR (1));
CREATE TABLE SEMSEC (
SSID VARCHAR (5) PRIMARY KEY,
SEM int,
SEC CHAR (1));
CREATE TABLE CLASS (
USN VARCHAR (10),
SSID VARCHAR (5),
PRIMARY KEY (USN, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN));
ALTER TABLE CLASS ADD FOREIGN KEY (SSID) REFERENCES SEMSEC (SSID);
CREATE TABLE SUBJECT (
SUBCODE VARCHAR (8),
TITLE VARCHAR (20),
SEM INT,
CREDITS INT,
PRIMARY KEY (SUBCODE));
CREATE TABLE IAMARKS (
USN VARCHAR (10),
SUBCODE VARCHAR (8),
SSID VARCHAR (5),
TEST1 INT ,
TEST2 INT,
TEST3 INT,
FINALIA INT,
PRIMARY KEY (USN, SUBCODE, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN));
ALTER TABLE IAMARKS ADD FOREIGN KEY (SUBCODE) REFERENCES SUBJECT (SUBCODE);
ALTER TABLE IAMARKS ADD FOREIGN KEY (SSID) REFERENCES SEMSEC (SSID);
INSERT INTO STUDENT VALUES (‘1RN13CS020′,’AKSHAY’,’BELAGAVI’,8877882,’M’);
INSERT INTO STUDENT VALUES (‘1RN13CS062′,’SANDHYA’,’BENGALURU’,772912,’F’);
INSERT INTO STUDENT VALUES (‘1RN13CS091′,’TEESHA’,’BENGALURU’,7712312,’F’);
INSERT INTO STUDENT VALUES (‘1RN13CS066′,’SUPRIYA’,’MANGALURU’,8871122,’F’);
INSERT INTO STUDENT VALUES (‘1RN14CS010′,’ABHAY’,’BENGALURU’,9900211,’M’);
INSERT INTO STUDENT VALUES (‘1RN14CS032′,’BHASKAR’,’BENGALURU’,992399,’M’);
INSERT INTO STUDENT VALUES (‘1RN14CS025′,’ASMI’,’BENGALURU’, 7894777,’F’);
INSERT INTO STUDENT VALUES (‘1RN15CS011′,’AJAY’,’TUMKUR’, 9845041,’M’);
INSERT INTO STUDENT VALUES (‘1RN15CS029′,’CHITRA’,’DAVANGERE’,
769621,’F’);
INSERT INTO STUDENT VALUES (‘1RN15CS045′,’JEEVA’,’BELLARY’, 850121,’M’);
INSERT INTO STUDENT VALUES (‘1RN15CS091′,’SANTOSH’,’MANGALURU’,
881231,’M’);
INSERT INTO STUDENT VALUES (‘1RN16CS045′,’ISMAIL’,’KALBURGI’,
990023,’M’);
INSERT INTO STUDENT VALUES (‘1RN16CS088′,’SAMEERA’,’SHIMOGA’,
990552,’F’);
INSERT INTO STUDENT VALUES (‘1RN16CS122′,’VINAYAKA’,’CHIKAMAGALUR’,
880011,’M’);
INSERT INTO SEMSEC VALUES (‘CSE8A’, 8,’A’);
INSERT INTO SEMSEC VALUES (‘CSE8B’, 8,’B’);
INSERT INTO SEMSEC VALUES (‘CSE8C’, 8,’C’);
INSERT INTO SEMSEC VALUES (‘CSE7A’, 7,’A’);
INSERT INTO SEMSEC VALUES (‘CSE7B’, 7,’B’);
INSERT INTO SEMSEC VALUES (‘CSE7C’, 7,’C’);
INSERT INTO SEMSEC VALUES (‘CSE6A’, 6,’A’);
INSERT INTO SEMSEC VALUES (‘CSE6B’, 6,’B’);
INSERT INTO SEMSEC VALUES (‘CSE6C’, 6,’C’);
INSERT INTO SEMSEC VALUES (‘CSE5A’, 5,’A’);
INSERT INTO SEMSEC VALUES (‘CSE5B’, 5,’B’);
INSERT INTO SEMSEC VALUES (‘CSE5C’, 5,’C’);
INSERT INTO SEMSEC VALUES (‘CSE4A’, 4,’A’);
INSERT INTO SEMSEC VALUES (‘CSE4B’, 4,’B’);
INSERT INTO SEMSEC VALUES (‘CSE4C’, 4,’C’);
INSERT INTO SEMSEC VALUES (‘CSE3A’, 3,’A’);
INSERT INTO SEMSEC VALUES (‘CSE3B’, 3,’B’);
INSERT INTO SEMSEC VALUES (‘CSE3C’, 3,’C’);
INSERT INTO SEMSEC VALUES (‘CSE2A’, 2,’A’);
INSERT INTO SEMSEC VALUES (‘CSE2B’, 2,’B’);
INSERT INTO SEMSEC VALUES (‘CSE2C’, 2,’C’);
INSERT INTO SEMSEC VALUES (‘CSE1A’, 1,’A’);
INSERT INTO SEMSEC VALUES (‘CSE1B’, 1,’B’);
INSERT INTO SEMSEC VALUES (‘CSE1C’, 1,’C’);
INSERT INTO CLASS VALUES (‘1RN13CS020′,’CSE8A’);
INSERT INTO CLASS VALUES (‘1RN13CS062′,’CSE8A’);
INSERT INTO CLASS VALUES (‘1RN13CS066′,’CSE8B’);
INSERT INTO CLASS VALUES (‘1RN13CS091′,’CSE8C’);
INSERT INTO CLASS VALUES (‘1RN14CS010′,’CSE7A’);
INSERT INTO CLASS VALUES (‘1RN14CS025′,’CSE7A’);
INSERT INTO CLASS VALUES (‘1RN14CS032′,’CSE7A’);
INSERT INTO CLASS VALUES (‘1RN15CS011′,’CSE4A’);
INSERT INTO CLASS VALUES (‘1RN15CS029′,’CSE4A’);
INSERT INTO CLASS VALUES (‘1RN15CS045′,’CSE4B’);
INSERT INTO CLASS VALUES (‘1RN15CS091′,’CSE4C’);
INSERT INTO CLASS VALUES (‘1RN16CS045′,’CSE3A’);
INSERT INTO CLASS VALUES (‘1RN16CS088′,’CSE3B’);
INSERT INTO CLASS VALUES (‘1RN16CS122′,’CSE3C’);
INSERT INTO SUBJECT VALUES (’10CS81′,’ACA’, 8, 4);
INSERT INTO SUBJECT VALUES (’10CS82′,’SSM’, 8, 4);
INSERT INTO SUBJECT VALUES (’10CS83′,’OOAD’, 8, 4);
INSERT INTO SUBJECT VALUES (’10CS84′,’ECS’, 8, 4);
INSERT INTO SUBJECT VALUES (’15CS51′, ‘ME’, 5, 4);
INSERT INTO SUBJECT VALUES (’15CS52′,’CN’, 5, 4);
INSERT INTO SUBJECT VALUES (’15CS41′,’M4′, 4, 4);
INSERT INTO SUBJECT VALUES (’15CS42′,’SE’, 4, 4);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
(‘1RN13CS091′,’10CS81′,’CSE8C’, 15, 16, 18);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
(‘1RN13CS091′,’10CS82′,’CSE8C’, 12, 19, 14);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
(‘1RN13CS091′,’10CS83′,’CSE8C’, 12, 19, 14);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
(‘1RN13CS091′,’10CS84′,’CSE8C’, 12, 19, 14);
SELECT S.*, SS.SEM, SS.SEC
FROM STUDENT S, SEMSEC SS, CLASS C
WHERE S.USN = C.USN AND
SS.SSID = C.SSID AND
SS.SEM = 8 AND
SS.SEc=’C’;
SELECT SS.SEM, SS.SEC, S.GENDER, COUNT(S.GENDER) AS COUNT
FROM STUDENT S, SEMSEC SS, CLASS C
WHERE S.USN = C.USN AND
SS.SSID = C.SSID
GROUP BY SS.SEM, SS.SEC, S.GENDER
ORDER BY SEM;
CREATE VIEW STU_TEST1_MARKS_VIEW
AS
SELECT TEST1, SUBCODE
FROM IAMARKS
WHERE USN = ‘1RN13CS091’;
select * from STU_TEST1_MARKS_VIEW;
update IAMARKS
set FINALIA=GREATEST(TEST1+TEST2,TEST3+TEST2,TEST1+TEST3);
SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,
(CASE
WHEN IA.FINALIA BETWEEN 17 AND 20 THEN ‘OUTSTANDING’
WHEN IA.FINALIA BETWEEN 12 AND 16 THEN ‘AVERAGE’
ELSE ‘WEAK’
END) AS CAT
FROM STUDENT S, SEMSEC SS, IAMARKS IA, SUBJECT SUB
WHERE S.USN = IA.USN AND
SS.SSID = IA.SSID AND
SUB.SUBCODE = IA.SUBCODE AND
SUB.SEM = 8;
Please follow and like us:
