.mysql_history

CREATE TABLE SALESMAN
(SALESMANID NUMBER(4),
NAME VARCHAR(20),
CITY VARCHAR(20),
COMMISSION VARCHAR(20),
PRIMARY KEY (SALESMANID));
CREATE TABLE SALESMAN (SALESMANID NUMBER(4), NAME VARCHAR(20), CITY VARCHAR(20), COMMISSION VARCHAR(20), PRIMARY KEY (SALESMANID));
DESC SALESMAN;
create user 'tushar' identified by 'pandeytushar8';
CREATE TABLE SALESMAN
(SALESMANID NUMBER(4),
NAME VARCHAR(20),
CITY VARCHAR(20),
COMMISSION VARCHAR(20),
PRIMARY KEY (SALESMANID));
CREATE TABLE SALESMAN (SALESMANID NUMBER(4), NAME VARCHAR(20), CITY VARCHAR(20), COMMISSION VARCHAR(20), PRIMARY KEY (SALESMANID));
DESC SALESMAN;
create database order
;
CREATE DATABASE orderdb;
CREATE DATABASE ORDERDB;
USE db1;
CREATE TABLE SALESMAN
(SALESMANID NUMBER(4),
NAME VARCHAR(20),
CITY VARCHAR(20),
COMMISSION VARCHAR(20),
PRIMARY KEY (SALESMANID));
CREATE TABLE SALESMAN (SALESMANID NUMBER(4), NAME VARCHAR(20), CITY VARCHAR(20), COMMISSION VARCHAR(20), PRIMARY KEY (SALESMANID));
CREATE TABLE salesman(salesmanid INT PRIMARY KEY AUTO_INCREMENT, name TEXT, city TEXT, commission TEXT);
DESC salesman;
CREATE TABLE customer(customerid INT PRIMARY KEY AUTO_INCREMENT, custname TEXT, city TEXT, grade INT, FOREIGN KEY(salesmanid) REFERENCES salesman(salesmanid) ON DELETE SET NULL);
CREATE TABLE customer(customerid INT PRIMARY KEY AUTO_INCREMENT, custname TEXT, city TEXT, grade INT, salesmanid INT ,FOREIGN KEY(salesmanid) REFERENCES salesman(salesmanid) ON DELETE SET NULL);
DESC customer;
CREATE TABLE orders(ordno INT PRIMARY KEY AUTO_INCREMENT, purchaseamt FLOAT, orddate DATE, customerid INT ,FOREIGN KEY(customerid) REFERENCES customer(customerid) ON DELETE SET NULL);
DESC orders;
INSERT INTO salesman VALUES(1000,'JOHN','BANGALORE','25%');
INSERT INTO salesman VALUES(2000,'RAVI','BANGALORE','20%');
INSERT INTO salesman VALUES(3000,'KUMAR','MYSORE','15%');
INSERT INTO salesman VALUES(4000,'SMITH','DELHI','30%');
INSERT INTO salesman VALUES(5000,'HARSHA','HYDERABAD','15%');
SELECT * FROM salesman;
INSERT INTO customer VALUES(10,'PREETHI','BANGALORE',100,1000);
INSERT INTO customer VALUES(11,'VIVEK','MANGALORE',300,1000);
INSERT INTO customer VALUES(12,'BHASKAR','CHENNAI',400,2000);
INSERT INTO customer VALUES(13,'CHETTAN','BANGALORE',200,2000);
INSERT INTO customer VALUES(14,'MANISH','BANGALORE',400,3000);
SELECT * FROM customer;
INSERT INTO orders VALUES(50,5000,'04-MAY-17',10,1000);
INSERT INTO orders VALUES(51,450,'20-JAN-17',10,2000);
INSERT INTO orders VALUES(52,1000,'24-FEB-17',13,2000);
INSERT INTO orders VALUES(53,3500,'13-APR-17',14,3000);
INSERT INTO orders VALUES(54,550,'09-MAR-17',12,2000);
INSERT INTO orders VALUES(50,5000,'04-MAY-17',1000);
INSERT INTO orders VALUES(51,450,'20-JAN-17',2000);
INSERT INTO orders VALUES(52,1000,'24-FEB-17',2000);
INSERT INTO orders VALUES(53,3500,'13-APR-17',3000);
INSERT INTO orders VALUES(54,550,'09-MAR-17',2000);
SELECT * FROM orders;
INSERT INTO orders VALUES(50,5000,STR_TO_DATE('04-MAY-17'),1000);
INSERT INTO orders VALUES(51,450,STR_TO_DATE('20-JAN-17'),2000);
INSERT INTO orders VALUES(52,1000,STR_TO_DATE('24-FEB-17'),2000);
INSERT INTO orders VALUES(53,3500,STR_TO_DATE('13-APR-17'),3000);
INSERT INTO orders VALUES(54,550,STR_TO_DATE('09-MAR-17'),2000);
SELECT * FROM orders;
INSERT INTO orders VALUES(50,5000,STR_TO_DATE('05-04-17', '%m-%d-%y'),1000);
INSERT INTO orders VALUES(50,5000,STR_TO_DATE('05-04-17', '%m-%d-%y'),10);
INSERT INTO orders VALUES(51,450,STR_TO_DATE('01-20-17', '%m-%d-%y'),11);
INSERT INTO orders VALUES(52,1000,STR_TO_DATE('02-24-17', '%m-%d-%y'),12);
INSERT INTO orders VALUES(53,3500,STR_TO_DATE('04-13-17', '%m-%d-%y'),13);
INSERT INTO orders VALUES(54,550,STR_TO_DATE('03-09-17', '%m-%d-%y'),14);
SELECT * FROM orders;
SELECT grade,COUNT(DISTINCT customerID)
FROM customer
GROUP BY grade
HAVING grade>(SELECT AVG(grade)
FROM customer
WHERE CITY='BANGALORE');
SELECT grade,COUNT(DISTINCT customerID) FROM customer GROUP BY grade HAVING grade>(SELECT AVG(grade) FROM customer WHERE CITY='BANGALORE');
SELECT salesmanID, NAME
FROM salesman A
WHERE 1<(SELECT COUNT(*)
FROM customer
WHERE salesmanID=A.salesmanID);
SELECT salesmanID, NAME FROM salesman A WHERE 1<(SELECT COUNT(*) FROM customer WHERE salesmanID=A.salesmanID);
SELECT salesman.salesmanID, NAME,CUSTNAME,COMMISSION
FROM salesman,customer
WHERE salesman.CITY=customer.CITY
UNION
SELECT salesmanID,NAME,'NO MATCH',COMMISSION
FROM salesman
WHERE NOT CITY=ANY
(SELECT CITY
FROM customer)
ORDER BY 2 .schema;
SELECT salesman.salesmanID, NAME,CUSTNAME,COMMISSION FROM salesman,customer WHERE salesman.CITY=customer.CITY UNION SELECT salesmanID,NAME,'NO MATCH',COMMISSION FROM salesman WHERE NOT CITY=ANY (SELECT CITY FROM customer) ORDER BY 2 .schema;
SELECT salesman.salesmanID, NAME,CUSTNAME,COMMISSION FROM salesman,customer WHERE salesman.CITY=customer.CITY UNION SELECT salesmanID,NAME,'NO MATCH',COMMISSION FROM salesman WHERE NOT CITY=ANY (SELECT CITY FROM customer) ORDER BY 2 DESC;
USE db1;
CREATE TABLE actor(actid INT PRIMARY KEY, actname TEXT, actgender TEXT)
;
DESC actor;
USE db1;
DESC actor;
CREATE TABLE director(dirid INT PRIMARY KEY, dirname TEXT, dirphone INT), );
CREATE TABLE director(dirid INT PRIMARY KEY, dirname TEXT, dirphone INT));
CREATE TABLE director(
  dirid INT PRIMARY KEY,
  dirname TEXT,
  dirphone INT
);
CREATE TABLE director(   dirid INT PRIMARY KEY,   dirname TEXT,   dirphone INT );
USE db1;
CREATE TABLE director(
  dirid INT PRIMARY KEY,
  dirname TEXT,
  dirphone INT
);
CREATE TABLE director(   dirid INT PRIMARY KEY,   dirname TEXT,   dirphone INT );
DESC director;
CREATE TABLE movies(
  movid INT PRIMARY KEY,
  movtitle TEXT,
  movyear INT,
  movlang TEXT,
  dirid INT,
  FOREIGN KEY(dirid) REFERENCES director(dirid)
);
CREATE TABLE movies(   movid INT PRIMARY KEY,   movtitle TEXT,   movyear INT,   movlang TEXT,   dirid INT,   FOREIGN KEY(dirid) REFERENCES director(dirid) );
DESC movies;
CREATE TABLE moviecast(
  actid INT,
  movid INT PRIMARY KEY,
  role TEXT,
  FOREIGN KEY(actid) REFERENCES actor(actid),
  FOREIGN KEY(movid) REFERENCES movies(movid)
);
CREATE TABLE moviecast(   actid INT,   movid INT PRIMARY KEY,   role TEXT,   FOREIGN KEY(actid) REFERENCES actor(actid),   FOREIGN KEY(movid) REFERENCES movies(movid) );
DESC moviecast;
CREATE TABLE rating(
  movid INT PRIMARY KEY,
  revstars TEXT,
  FOREIGN KEY(movid) REFERENCES movies(movid)
);
CREATE TABLE rating(   movid INT PRIMARY KEY,   revstars TEXT,   FOREIGN KEY(movid) REFERENCES movies(movid) );
DESC rating;
INSERT INTO actor
VALUES(301, 'anushka', 'f');
INSERT INTO actor VALUES(301, 'anushka', 'f');
INSERT INTO actor
VALUES(302, 'prabhas', 'm');
INSERT INTO actor VALUES(302, 'prabhas', 'm');
INSERT INTO actor
VALUES(303, 'punith', 'm');
INSERT INTO actor VALUES(303, 'punith', 'm');
INSERT INTO actor
VALUES(304, 'sudeep', 'm');
INSERT INTO actor VALUES(304, 'sudeep', 'm');
SELECT *
FROM actor;
SELECT * FROM actor;
DROP TABLE actor;
DROP TABLE moviecast;
DROP TABLE movies;
DROP TABLE rating;
DROP TABLE movies;
DROP TABLE director;
DROP TABLE actor;
USE db1;
CREATE TABLE actor(
  actid INT PRIMARY KEY,
  actname TEXT,
  actgender TEXT
) DESC actor;
CREATE TABLE actor(   actid INT PRIMARY KEY,   actname TEXT,   actgender TEXT ) DESC actor;
CREATE TABLE director(
  dirid INT PRIMARY KEY,
  dirname TEXT,
  dirphone INT
);
CREATE TABLE director(   dirid INT PRIMARY KEY,   dirname TEXT,   dirphone INT );
DESC director;
CREATE TABLE movies(
  movid INT PRIMARY KEY,
  movtitle TEXT,
  movyear INT,
  movlang TEXT,
  dirid INT,
  FOREIGN KEY(dirid) REFERENCES director(dirid)
);
CREATE TABLE movies(   movid INT PRIMARY KEY,   movtitle TEXT,   movyear INT,   movlang TEXT,   dirid INT,   FOREIGN KEY(dirid) REFERENCES director(dirid) );
DESC movies;
CREATE TABLE moviecast(
  actid INT,
  movid INT PRIMARY KEY,
  role TEXT,
  FOREIGN KEY(actid) REFERENCES actor(actid),
  FOREIGN KEY(movid) REFERENCES movies(movid)
);
CREATE TABLE moviecast(   actid INT,   movid INT PRIMARY KEY,   role TEXT,   FOREIGN KEY(actid) REFERENCES actor(actid),   FOREIGN KEY(movid) REFERENCES movies(movid) );
DESC moviecast;
CREATE TABLE rating(
  movid INT PRIMARY KEY,
  revstars TEXT,
  FOREIGN KEY(movid) REFERENCES movies(movid)
);
CREATE TABLE rating(   movid INT PRIMARY KEY,   revstars TEXT,   FOREIGN KEY(movid) REFERENCES movies(movid) );
DESC rating;
DROP TABLE moviecast;
DROP TABLE movies;
DROP TABLE ratings;
DROP TABLE rating;
DROP TABLE actor;
USE db1;
DROP TABLE moviecast;
DROP TABLE rating;
DROP TABLE actor;
DROP TABLE movies;
DROP TABLE director;
USE db1;
CREATE TABLE actor(
  actid INT PRIMARY KEY,
  actname TEXT,
  actgender TEXT
);
CREATE TABLE actor(   actid INT PRIMARY KEY,   actname TEXT,   actgender TEXT );
DESC actor;
CREATE TABLE director(
  dirid INT PRIMARY KEY,
  dirname TEXT,
  dirphone INT
);
CREATE TABLE director(   dirid INT PRIMARY KEY,   dirname TEXT,   dirphone INT );
DESC director;
CREATE TABLE movies(
  movid INT PRIMARY KEY,
  movtitle TEXT,
  movyear INT,
  movlang TEXT,
  dirid INT,
  FOREIGN KEY(dirid) REFERENCES director(dirid)
);
CREATE TABLE movies(   movid INT PRIMARY KEY,   movtitle TEXT,   movyear INT,   movlang TEXT,   dirid INT,   FOREIGN KEY(dirid) REFERENCES director(dirid) );
DESC movies;
CREATE TABLE moviecast(
  actid INT,
  movid INT PRIMARY KEY,
  role TEXT,
  FOREIGN KEY(actid) REFERENCES actor(actid),
  FOREIGN KEY(movid) REFERENCES movies(movid)
);
CREATE TABLE moviecast(   actid INT,   movid INT PRIMARY KEY,   role TEXT,   FOREIGN KEY(actid) REFERENCES actor(actid),   FOREIGN KEY(movid) REFERENCES movies(movid) );
DESC moviecast;
CREATE TABLE rating(
  movid INT PRIMARY KEY,
  revstars TEXT,
  FOREIGN KEY(movid) REFERENCES movies(movid)
);
CREATE TABLE rating(   movid INT PRIMARY KEY,   revstars TEXT,   FOREIGN KEY(movid) REFERENCES movies(movid) );
DESC rating;
INSERT INTO actor
VALUES(301, 'actor1', 'f');
INSERT INTO actor VALUES(301, 'actor1', 'f');
INSERT INTO actor
VALUES(302, 'actor 2', 'm');
INSERT INTO actor VALUES(302, 'actor 2', 'm');
INSERT INTO actor
VALUES(303, 'actor3', 'm');
INSERT INTO actor VALUES(303, 'actor3', 'm');
INSERT INTO actor
VALUES(304, 'actor4', 'm');
INSERT INTO actor VALUES(304, 'actor4', 'm');
SELECT *
FROM actor;
SELECT * FROM actor;
INSERT INTO director
VALUES(60, 'director1', 9191919191);
INSERT INTO director VALUES(60, 'director1', 9191919191);
ALTER TABLE director MODIFY dirphone TEXT;
INSERT INTO director VALUES(60, 'director1', '9191919191');
INSERT INTO director
VALUES(61, 'hitchcock', '818181818');
INSERT INTO director VALUES(61, 'hitchcock', '818181818');
INSERT INTO director
VALUES(62, 'director3', '7171717171');
INSERT INTO director VALUES(62, 'director3', '7171717171');
INSERT INTO director
VALUES(63, 'steven spielberg', '6161616161');
INSERT INTO director VALUES(63, 'steven spielberg', '6161616161');
SELECT *
FROM director;
SELECT * FROM director;
INSERT INTO movies
VALUES(1001, 'movie1', 2017, 'english', 60);
INSERT INTO movies VALUES(1001, 'movie1', 2017, 'english', 60);
INSERT INTO movies
VALUES(1002, 'movie2', 2015, 'hindi', 60);
INSERT INTO movies VALUES(1002, 'movie2', 2015, 'hindi', 60);
INSERT INTO movies
VALUES(1003, 'movie3', 2008, 'english', 61);
INSERT INTO movies VALUES(1003, 'movie3', 2008, 'english', 61);
INSERT INTO movies
VALUES(1004, 'war horse', 2011, 'english', 63);
INSERT INTO movies VALUES(1004, 'war horse', 2011, 'english', 63);
SELECT *
FROM movies;
SELECT * FROM movies;
INSERT INTO moviecast
VALUES(301, 1002, 'heroine');
INSERT INTO moviecast VALUES(301, 1002, 'heroine');
INSERT INTO moviecast
VALUES(301, 1001, 'heroine');
INSERT INTO moviecast VALUES(301, 1001, 'heroine');
INSERT INTO moviecast
VALUES(303, 1003, 'hero');
INSERT INTO moviecast VALUES(303, 1003, 'hero');
INSERT INTO moviecast
VALUES(303, 1002, 'guest');
INSERT INTO moviecast VALUES(303, 1002, 'guest');
INSERT INTO moviecast
VALUES(304, 1004, 'hero');
INSERT INTO moviecast VALUES(304, 1004, 'hero');
SELECT *
FROM moviecast;
SELECT * FROM moviecast;
INSERT INTO rating
VALUES(1001, 4);
INSERT INTO rating VALUES(1001, 4);
INSERT INTO rating
VALUES(1002, 2);
INSERT INTO rating VALUES(1002, 2);
INSERT INTO rating
VALUES(1003, 5);
INSERT INTO rating VALUES(1003, 5);
INSERT INTO rating
VALUES(1004, 4);
INSERT INTO rating VALUES(1004, 4);
SELECT *
FROM rating;
SELECT * FROM rating;
SELECT m.movtitle from movies m, director d WHEN d.dirname='hitchcock' AND d.dirid=m.dirid;
SELECT m.movtitle FROM movies m, director d WHEN d.dirname='hitchcock' AND d.dirid=m.dirid;
SELECT M.movtitle FROM movies M, director D WHERE D.dirname='hitchcock' AND D.dirid=M.dirid;
SELECT movtitle
FROM movies m,
  moviecast mv
WHERE m.movid = mv.movid
  AND actid IN (
    SELECT actid
    FROM moviecast
    group by actid
    having count (actid) > 1
  )
group by movtitle
having count(*) > 1;
SELECT movtitle FROM movies m,   moviecast mv WHERE m.movid = mv.movid   AND actid IN (     SELECT actid     FROM moviecast     group by actid     having count (actid) > 1   ) group by movtitle having count(*) > 1;
SELECT movtitle
FROM movies m,
  moviecast mv
WHERE m.movid = mv.movid
  AND actid IN (
    SELECT actid
    FROM moviecast
    group by actid
    having COUNT(actid) > 1
  )
group by movtitle
having COUNT *
) > 1;
SELECT movtitle FROM movies m,   moviecast mv WHERE m.movid = mv.movid   AND actid IN (     SELECT actid     FROM moviecast     group by actid     having COUNT(actid) > 1   ) group by movtitle having COUNT * ) > 1;
SELECT movtitle
FROM movies m,
  moviecast mv
WHERE m.movid = mv.movid
  AND actid IN (
    SELECT actid
    FROM moviecast
    GROUP BY actid
    HAVING COUNT(actid) > 1
  )
GROUP BY movtitle
HAVING COUNT *
) > 1;
SELECT movtitle FROM movies m,   moviecast mv WHERE m.movid = mv.movid   AND actid IN (     SELECT actid     FROM moviecast     GROUP BY actid     HAVING COUNT(actid) > 1   ) GROUP BY movtitle HAVING COUNT * ) > 1;
SELECT movtitle
FROM movies M,
  moviecast MV
WHERE M.movid = MV.movid
  AND actid IN (
    SELECT actid
    FROM moviecast
    GROUP BY actid
    HAVING COUNT(actid) > 1
  )
GROUP BY movtitle
HAVING COUNT *
) > 1;
SELECT movtitle FROM movies M,   moviecast MV WHERE M.movid = MV.movid   AND actid IN (     SELECT actid     FROM moviecast     GROUP BY actid     HAVING COUNT(actid) > 1   ) GROUP BY movtitle HAVING COUNT * ) > 1;
SELECT movtitle
FROM movies M,
  moviecast MV
WHERE M.movid = MV.movid
  AND actid IN (
    SELECT actid
    FROM moviecast
    GROUP BY actid
    HAVING COUNT(actid) > 1
  )
GROUP BY movtitle
HAVING COUNT(*) > 1;
SELECT movtitle FROM movies M,   moviecast MV WHERE M.movid = MV.movid   AND actid IN (     SELECT actid     FROM moviecast     GROUP BY actid     HAVING COUNT(actid) > 1   ) GROUP BY movtitle HAVING COUNT(*) > 1;
INSERT INTO movies
VALUES(1004, 'movie4', 2011, 'english', 63);
INSERT INTO movies VALUES(1004, 'movie4', 2011, 'english', 63);
INSERT INTO movies VALUES(1005, 'movie4', 2011, 'english', 63);
SELECT movtitle FROM movies M,   moviecast MV WHERE M.movid = MV.movid   AND actid IN (     SELECT actid     FROM moviecast     GROUP BY actid     HAVING COUNT(actid) > 1   ) GROUP BY movtitle HAVING COUNT(*) > 1;
INSERT INTO moviecast
VALUES(304, 1003, 'sidehero');
INSERT INTO moviecast VALUES(304, 1003, 'sidehero');
INSERT INTO moviecast VALUES(304, 1005, 'hero');
SELECT movtitle FROM movies M,   moviecast MV WHERE M.movid = MV.movid   AND actid IN (     SELECT actid     FROM moviecast     GROUP BY actid     HAVING COUNT(actid) > 1   ) GROUP BY movtitle HAVING COUNT(*) > 1;
SELECT movtitle FROM movies M,   moviecast MV WHERE M.movid = MV.movid   AND actid IN (     SELECT actid     FROM moviecast     GROUP BY actid     HAVING COUNT(actid) >= 1   ) GROUP BY movtitle HAVING COUNT(*) > 1;
SELECT movtitle FROM movies M,   moviecast MV WHERE M.movid = MV.movid   AND actid IN (     SELECT actid     FROM moviecast     GROUP BY actid     HAVING COUNT(actid) > 0   ) GROUP BY movtitle HAVING COUNT(*) > 1;
SELECT movtitle FROM movies M,   moviecast MV WHERE M.movid = MV.movid   AND actid IN (     SELECT actid     FROM moviecast     GROUP BY actid     HAVING COUNT(actid) > 1   ) GROUP BY movtitle HAVING COUNT(*) > 1;
SELECT actname,
  movtitle,
  movyear
FROM actor a
  JOIN moviecast c ON a.actid = c.actid
  JOIN movies M ON c.movid = M.movid
WHERE M.movyear NOT BETWEEN 2000 AND 2015;
SELECT actname,   movtitle,   movyear FROM actor a   JOIN moviecast c ON a.actid = c.actid   JOIN movies M ON c.movid = M.movid WHERE M.movyear NOT BETWEEN 2000 AND 2015;
SELECT movtitle,
  max(revstars)
FROM movies
  INNER JOIN rating USING (movid)
GROUP BY movtitle
HAVING MAX(revstars) > 0
ORDER BY movtitle;
SELECT movtitle,   max(revstars) FROM movies   INNER JOIN rating USING (movid) GROUP BY movtitle HAVING MAX(revstars) > 0 ORDER BY movtitle;
UPDATE rating
SET revstars = 5
WHERE movid IN (
    SELECT movid
    FROM movies
    WHERE dirid IN (
        SELECT dirid
        FROM director
        WHERE dirname = 'steven spielberg'
      )
  );
UPDATE rating SET revstars = 5 WHERE movid IN (     SELECT movid     FROM movies     WHERE dirid IN (         SELECT dirid         FROM director         WHERE dirname = 'steven spielberg'       )   );
SELECT *
FROM rating;
SELECT * FROM rating;
USE db1;
CREATE TABLE STUDENT(
USN TEXT) PRIMARY KEY,
SNAME TEXT),
ADDRESS TEXT),
PHONE TEXT,
GENDER CHAR(1));
CREATE TABLE STUDENT( USN TEXT) PRIMARY KEY, SNAME TEXT), ADDRESS TEXT), PHONE TEXT, GENDER CHAR(1));
CREATE TABLE STUDENT(USN TEXT PRIMARY KEY,SNAME TEXT,ADDRESS TEXT,PHONE TEXT,GENDER CHAR(1));
USE db1;
CREATE TABLE STUDENT(USN TEXT PRIMARY KEY,SNAME TEXT,ADDRESS TEXT,PHONE TEXT,GENDER CHAR(1));
CREATE TABLE STUDENT(USN TEXT(10) PRIMARY KEY,SNAME TEXT,ADDRESS TEXT,PHONE TEXT,GENDER CHAR(1));
CREATE TABLE STUDENT(USN VARCHAR(10) PRIMARY KEY,SNAME TEXT,ADDRESS TEXT,PHONE TEXT,GENDER CHAR(1));
DROP STUDENT;
DROP TABLE STUDENT;
prompt 'SQL';
promt sql>
;
promt sql>;
promt sql;
prompt sql>;
\c
\c;
clear;
SELECT * FROM movies;
DROP TABLE STUDENT;
CREATE TABLE STUDENT(USN VARCHAR(10) PRIMARY KEY,SNAME VARCHAR(25),ADDRESS VARCHAR(25),PHONE NUMBER(10),GENDER CHAR(1));
CREATE TABLE STUDENT(USN VARCHAR(10) PRIMARY KEY,SNAME VARCHAR(25),ADDRESS VARCHAR(25),PHONE TEXT(10),GENDER CHAR(1));
DESC STUDENT;
CREATE TABLE SEMSEC(SSID VARCHAR(5) PRIMARY KEY, SEM INT, SEC CHAR(1));
DESC SEMSEC;
CREATE TABLE CLASS( USN VARCHAR(10), SSID VARCHAR(5), PRIMARY KEY(USN,SSID), FOREIGN KEY(USN) REFERENCES STUDENT(USN), FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID));
DESC CLASS;
CREATE TABLE SUBJECT(SUBCODE VARCHAR(8),TITLE VARCHAR(20),SEM INT,CREDITS INT,PRIMARY KEY(SUBCODE));
DESC SUBJECT
;
CREATE TABLE IAMARKS(USN VARCHAR(10),SUBCODE VARCHAR(8),SSID VARCHAR(5),TEST1 INT,TEST2 INT,TEST3 INT,FINALIA INT,PRIMARY KEY(SUBCODE,USN,SSID),FOREIGN KEY(USN) REFERENCES STUDENT(USN),FOREIGN KEY(SUBCODE) REFERENCES SUBJECT(SUBCODE),FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID));
DESC IAMARKS;
INSERT INTO STUDENT VALUES ('1BY18CS180', 'TUSHAR P', 'BLR', '1234567890', 'M');
INSERT INTO STUDENT VALUES ('1BY18CS101', 'STUDENT2', 'BLR', '1234567890', 'F');
INSERT INTO STUDENT VALUES ('1BY18CS102', 'STUDENT3', 'BLR', '1234567890', 'F');
INSERT INTO STUDENT VALUES ('1BY18CS103', 'STUDENT4', 'BLR', '1234567890', 'F');
INSERT INTO STUDENT VALUES ('1BY18CS104', 'STUDENT5', 'BLR', '1234567890', 'M');
INSERT INTO STUDENT VALUES ('1BY18CS105', 'STUDENT6', 'BLR', '1234567890', 'M');
INSERT INTO STUDENT VALUES ('1BY18CS106', 'STUDENT7', 'BLR', '1234567890', 'F');
INSERT INTO STUDENT VALUES ('1BY18CS107', 'STUDENT8', 'BLR', '1234567890', 'M');
INSERT INTO STUDENT VALUES ('1BY18CS108', 'STUDENT9', 'BLR', '1234567890', 'F');
INSERT INTO STUDENT VALUES ('1BY18CS109', 'STUDENT10', 'BLR', '1234567890', 'M');
SELECT * FROM STUDENT;
INSERT INTO SEMSEC VALUES ('CSE5A', 5, 'A');
INSERT INTO SEMSEC VALUES ('CSE5B', 5, 'B');
INSERT INTO SEMSEC VALUES ('CSE5C', 5, 'C');
SELECT * FROM SEMSEC;
INSERT INTO CLASS VALUES ('1BY18CS180', 'CSE5C');
INSERT INTO CLASS VALUES ('1BY18CS101', 'CSE5A');
INSERT INTO CLASS VALUES ('1BY18CS102', 'CSE5A');
INSERT INTO CLASS VALUES ('1BY18CS103', 'CSE5A');
INSERT INTO CLASS VALUES ('1BY18CS104', 'CSE5B');
INSERT INTO CLASS VALUES ('1BY18CS105', 'CSE5B');
INSERT INTO CLASS VALUES ('1BY18CS106', 'CSE5B');
INSERT INTO CLASS VALUES ('1BY18CS107', 'CSE5C');
INSERT INTO CLASS VALUES ('1BY18CS108', 'CSE5C');
INSERT INTO CLASS VALUES ('1BY18CS109', 'CSE5C');
SELECT * FROM CLASS;
INSERT INTO SUBJECT VALUES ('15CS51', 'ME', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS52', 'CN', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS53', 'DBMS', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS54', 'ATC', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS55', 'RADP', 5, 3);
INSERT INTO SUBJECT VALUES ('15CS56', 'UNIX', 5, 3);
SELECT * FROM SUBJECT;
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '10CS51', 'CSE5C', 15, 16, 18);
DELETE * FROM SUBJECT;
DELETE FROM SUBJECT;
SELECT * FROM SUBJECT;
INSERT INTO SUBJECT VALUES ('18CS51', 'ME', 5, 4);
INSERT INTO SUBJECT VALUES ('18CS52', 'CN', 5, 4);
INSERT INTO SUBJECT VALUES ('18CS53', 'DBMS', 5, 4);
INSERT INTO SUBJECT VALUES ('18CS54', 'ATC', 5, 4);
INSERT INTO SUBJECT VALUES ('18CS55', 'RADP', 5, 3);
INSERT INTO SUBJECT VALUES ('18CS56', 'UNIX', 5, 3);
SELECT * FROM SUBJECT;
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS51', 'CSE5C', 15, 16, 18);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS52', 'CSE5C', 18, 19, 14);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS53', 'CSE5C', 19, 15, 20);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS54', 'CSE5C', 20, 16, 19);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS55', 'CSE5C', 20, 20, 20);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS56', 'CSE5C', 17, 18, 19);
SELECT * FROM IAMARKS;
DELETE FROM IAMARKS;
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS51', 'CSE5C', 15, 16, 18, 45);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS52', 'CSE5C', 18, 19, 14, 45);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS53', 'CSE5C', 19, 15, 20, 45);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS54', 'CSE5C', 20, 16, 19, 45);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS55', 'CSE5C', 20, 20, 20, 45);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS56', 'CSE5C', 17, 18, 19, 45);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3, FINALIA) VALUES ('1BY18CS180', '18CS51', 'CSE5C', 15, 16, 18, 45);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3, FINALIA) VALUES ('1BY18CS180', '18CS52', 'CSE5C', 18, 19, 14, 45);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3, FINALIA) VALUES ('1BY18CS180', '18CS53', 'CSE5C', 19, 15, 20, 45);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3, FINALIA) VALUES ('1BY18CS180', '18CS54', 'CSE5C', 20, 16, 19, 45);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3, FINALIA) VALUES ('1BY18CS180', '18CS55', 'CSE5C', 20, 20, 20, 45);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3, FINALIA) VALUES ('1BY18CS180', '18CS56', 'CSE5C', 17, 18, 19, 45);
SELECT * FROM IAMARKS;
SELECT S.*, SS.SEM, SS.SEC FROM STUDENT S, SEMSEC SS, CLASS C WHERE SS.SEM=5 AND SS.SEC='C' AND SS.SSID=C.SSID AND S.USN=C.USN;
SELECT SS.SEM, SS.SEC, S.GENDER, COUNT(S.GENDER) AS CNT FROM STUDENT S, SEMSEC SS, CLASS C WHERE S.USN=C.USN AND C.SSID=SS.SSID GROUP BY SS.SEM, SS.SEC, S.GENDER;
CREATE VIEW TMP AS SELECT IAM.TEST1, IAM.SUBCODE, SBJ.TITLE FROM IAMARKS IAM, SUBJECT SBJ WHERE IAM.USN='1BY18CS180';
SELECT * FROM TMP;
CREATE VIEW TMP AS SELECT IAM.TEST1, IAM.SUBCODE, SBJ.TITLE FROM IAMARKS IAM, SUBJECT SBJ WHERE IAM.USN='1BY18CS180' SBJ.SUBCODE=IAM.SUBCODE;
CREATE VIEW TMP AS SELECT IAM.TEST1, IAM.SUBCODE, SBJ.TITLE FROM IAMARKS IAM, SUBJECT SBJ WHERE IAM.USN='1BY18CS180', SBJ.SUBCODE=IAM.SUBCODE;
CREATE VIEW TMP AS SELECT IAM.TEST1, IAM.SUBCODE, SBJ.TITLE FROM IAMARKS IAM, SUBJECT SBJ WHERE IAM.USN='1BY18CS180' AND SBJ.SUBCODE=IAM.SUBCODE;
DROP TABLE TMP;
DROP VIEW TMP;
CREATE VIEW TMP AS SELECT IAM.TEST1, IAM.SUBCODE, SBJ.TITLE FROM IAMARKS IAM, SUBJECT SBJ WHERE IAM.USN='1BY18CS180' AND SBJ.SUBCODE=IAM.SUBCODE;
SELECT * FROM TMP;
DELETE FROM IAMARKS
;
DROP TABLE IAMARKS
;
DROP TABLE SUBJECTS;
DROP TABLE SUBJECT;
DROP TABLE CLASS;
DROP TABLE SECSEM;
DROP TABLE SEMSEC;
DROP TABLE STUDENT;
DROP VIEW TMP;
CREATE TABLE STUDENT(USN VARCHAR(10) PRIMARY KEY,SNAME VARCHAR(25),ADDRESS VARCHAR(25),PHONE TEXT(10),GENDER CHAR(1));
DESC STUDENT;
CREATE TABLE SEMSEC(SSID VARCHAR(5) PRIMARY KEY, SEM INT, SEC CHAR(1));
DESC SEMSEC;
CREATE TABLE CLASS( USN VARCHAR(10), SSID VARCHAR(5), PRIMARY KEY(USN,SSID), FOREIGN KEY(USN) REFERENCES STUDENT(USN), FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID));
DESC CLASS;
CREATE TABLE SUBJECT(SUBCODE VARCHAR(8),TITLE VARCHAR(20),SEM INT,CREDITS INT,PRIMARY KEY(SUBCODE));
DESC SUBJECT;
CREATE TABLE IAMARKS(USN VARCHAR(10),SUBCODE VARCHAR(8),SSID VARCHAR(5),TEST1 INT,TEST2 INT,TEST3 INT,FINALIA INT,PRIMARY KEY(SUBCODE,USN,SSID),FOREIGN KEY(USN) REFERENCES STUDENT(USN),FOREIGN KEY(SUBCODE) REFERENCES SUBJECT(SUBCODE),FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID));
DESC IAMARKS;
INSERT INTO STUDENT VALUES ('1BY18CS180', 'TUSHAR P', 'BLR', '1234567890', 'M');
INSERT INTO STUDENT VALUES ('1BY18CS101', 'STUDENT2', 'BLR', '1234567890', 'F');
INSERT INTO STUDENT VALUES ('1BY18CS102', 'STUDENT3', 'BLR', '1234567890', 'F');
INSERT INTO STUDENT VALUES ('1BY18CS103', 'STUDENT4', 'BLR', '1234567890', 'F');
INSERT INTO STUDENT VALUES ('1BY18CS104', 'STUDENT5', 'BLR', '1234567890', 'M');
INSERT INTO STUDENT VALUES ('1BY18CS105', 'STUDENT6', 'BLR', '1234567890', 'M');
INSERT INTO STUDENT VALUES ('1BY18CS106', 'STUDENT7', 'BLR', '1234567890', 'F');
INSERT INTO STUDENT VALUES ('1BY18CS107', 'STUDENT8', 'BLR', '1234567890', 'M');
INSERT INTO STUDENT VALUES ('1BY18CS108', 'STUDENT9', 'BLR', '1234567890', 'F');
INSERT INTO STUDENT VALUES ('1BY18CS109', 'STUDENT10', 'BLR', '1234567890', 'M');
SELECT * FROM STUDENT;
INSERT INTO SEMSEC VALUES ('CSE5A', 5, 'A');
INSERT INTO SEMSEC VALUES ('CSE5B', 5, 'B');
INSERT INTO SEMSEC VALUES ('CSE5C', 5, 'C');
SELECT * FROM SEMSEC;
INSERT INTO CLASS VALUES ('1BY18CS180', 'CSE5C');
INSERT INTO CLASS VALUES ('1BY18CS101', 'CSE5A');
INSERT INTO CLASS VALUES ('1BY18CS102', 'CSE5A');
INSERT INTO CLASS VALUES ('1BY18CS103', 'CSE5A');
INSERT INTO CLASS VALUES ('1BY18CS104', 'CSE5B');
INSERT INTO CLASS VALUES ('1BY18CS105', 'CSE5B');
INSERT INTO CLASS VALUES ('1BY18CS106', 'CSE5B');
INSERT INTO CLASS VALUES ('1BY18CS107', 'CSE5C');
INSERT INTO CLASS VALUES ('1BY18CS108', 'CSE5C');
INSERT INTO CLASS VALUES ('1BY18CS109', 'CSE5C');
SELECT * FROM CLASS;
INSERT INTO SUBJECT VALUES ('15CS51', 'ME', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS52', 'CN', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS53', 'DBMS', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS54', 'ATC', 5, 4);
INSERT INTO SUBJECT VALUES ('15CS55', 'RADP', 5, 3);
INSERT INTO SUBJECT VALUES ('15CS56', 'UNIX', 5, 3);
SELECT * FROM SUBJECT;
DELETE FROM SUBJECT;
INSERT INTO SUBJECT VALUES ('18CS51', 'ME', 5, 4);
INSERT INTO SUBJECT VALUES ('18CS52', 'CN', 5, 4);
INSERT INTO SUBJECT VALUES ('18CS53', 'DBMS', 5, 4);
INSERT INTO SUBJECT VALUES ('18CS54', 'ATC', 5, 4);
INSERT INTO SUBJECT VALUES ('18CS55', 'RADP', 5, 3);
INSERT INTO SUBJECT VALUES ('18CS56', 'UNIX', 5, 3);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS51', 'CSE5C', 15, 16, 18);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS52', 'CSE5C', 18, 19, 14);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS53', 'CSE5C', 19, 15, 20);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS54', 'CSE5C', 20, 16, 19);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS55', 'CSE5C', 20, 20, 20);
INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES ('1BY18CS180', '18CS56', 'CSE5C', 17, 18, 19);
SELECT * FROM IAMARKS;
SELECT S.*, SS.SEM, SS.SEC FROM STUDENT S, SEMSEC SS, CLASS C WHERE SS.SEM=5 AND SS.SEC='C' AND SS.SSID=C.SSID AND S.USN=C.USN;
SELECT SS.SEM, SS.SEC, S.GENDER, COUNT(S.GENDER) AS CNT FROM STUDENT S, SEMSEC SS, CLASS C WHERE S.USN=C.USN AND C.SSID=SS.SSID GROUP BY SS.SEM, SS.SEC, S.GENDER;
CREATE VIEW TMP AS SELECT IAM.TEST1, IAM.SUBCODE, SBJ.TITLE FROM IAMARKS IAM, SUBJECT SBJ WHERE IAM.USN='1BY18CS180' AND SBJ.SUBCODE=IAM.SUBCODE;
SELECT * FROM TMP;
USE db1;
CREATE OR REPLACE PROCEDURE AVGMARKS
IS
CURSOR C_IAMARKS IS
SELECT GREATEST(TEST1,TEST2) AS A, GREATEST(TEST1,TEST3) AS B, GREATEST(TEST3,TEST2) AS C
FROM IAMARKS
WHERE FINALIA IS NULL
FOR UPDATE;
CREATE OR REPLACE PROCEDURE AVGMARKS IS CURSOR C_IAMARKS IS SELECT GREATEST(TEST1,TEST2) AS A, GREATEST(TEST1,TEST3) AS B, GREATEST(TEST3,TEST2) AS C FROM IAMARKS WHERE FINALIA IS NULL FOR UPDATE;
C_A NUMBER;
C_B NUMBER;
C_C NUMBER;
C_SM NUMBER;
C_AV NUMBER;
BEGIN
OPEN C_IAMARKS;
BEGIN OPEN C_IAMARKS;
LOOP
FETCH C_IAMARKS INTO C_A, C_B, C_C;
LOOP FETCH C_IAMARKS INTO C_A, C_B, C_C;
EXIT WHEN C_IAMARKS%NOTFOUND;
--DBMS_OUTPUT.PUT_LINE(C_A || ' ' || C_B || ' ' || C_C);
IF (C_A != C_B) THEN
C_SM:=C_A+C_B;
IF (C_A != C_B) THEN C_SM:=C_A+C_B;
ELSE
C_SM:=C_A+C_C;
ELSE C_SM:=C_A+C_C;
END IF;
C_AV:=C_SM/2;
--DBMS_OUTPUT.PUT_LINE('SUM = '||C_SM);
--DBMS_OUTPUT.PUT_LINE('AVERAGE = '||C_AV);
UPDATE IAMARKS SET FINALIA=C_AV WHERE CURRENT OF C_IAMARKS;
END LOOP;
CLOSE C_IAMARKS;
END;
/
SELECT * FROM IAMARKS;
/   SELECT * FROM IAMARKS;
BEGIN
AVGMARKS;
BEGIN AVGMARKS;
END;
SELECT * FROM IAMARKS;
CREATE OR REPLACE PROCEDURE AVGMARKS
IS
CURSOR C_IAMARKS IS
SELECT GREATEST(TEST1,TEST2) AS A, GREATEST(TEST1,TEST3) AS B, GREATEST(TEST3,TEST2) AS C
FROM IAMARKS
WHERE FINALIA IS NULL
FOR UPDATE;
CREATE OR REPLACE PROCEDURE AVGMARKS IS CURSOR C_IAMARKS IS SELECT GREATEST(TEST1,TEST2) AS A, GREATEST(TEST1,TEST3) AS B, GREATEST(TEST3,TEST2) AS C FROM IAMARKS WHERE FINALIA IS NULL FOR UPDATE;
C_A NUMBER;
C_B NUMBER;
C_C NUMBER;
C_SM NUMBER;
C_AV NUMBER;
BEGIN
OPEN C_IAMARKS;
BEGIN OPEN C_IAMARKS;
LOOP
FETCH C_IAMARKS INTO C_A, C_B, C_C;
LOOP FETCH C_IAMARKS INTO C_A, C_B, C_C;
EXIT WHEN C_IAMARKS%NOTFOUND;
--DBMS_OUTPUT.PUT_LINE(C_A || ' ' || C_B || ' ' || C_C);
IF (C_A != C_B) THEN
C_SM:=C_A+C_B;
IF (C_A != C_B) THEN C_SM:=C_A+C_B;
ELSE
C_SM:=C_A+C_C;
ELSE C_SM:=C_A+C_C;
END IF;
C_AV:=C_SM/2;
--DBMS_OUTPUT.PUT_LINE('SUM = '||C_SM);
--DBMS_OUTPUT.PUT_LINE('AVERAGE = '||C_AV);
UPDATE IAMARKS SET FINALIA=C_AV WHERE CURRENT OF C_IAMARKS;
END LOOP;
CLOSE C_IAMARKS;
END;
/
SELECT * FROM IAMARKS;
/   SELECT * FROM IAMARKS;
BEGIN
AVGMARKS;
BEGIN AVGMARKS;
END;
CREATE OR REPLACE PROCEDURE AVGMARKS
IN CURSOR
IN C_IAMARKS
SELECT GREATEST(TEST1,TEST2) AS A, GREATEST(TEST1,TEST3) AS B, GREATEST(TEST3,TEST2) AS C FROM IAMARKS WHERE FINALIA IS NULL
FOR UPDATE;
CREATE OR REPLACE PROCEDURE AVGMARKS IN CURSOR IN C_IAMARKS SELECT GREATEST(TEST1,TEST2) AS A, GREATEST(TEST1,TEST3) AS B, GREATEST(TEST3,TEST2) AS C FROM IAMARKS WHERE FINALIA IS NULL FOR UPDATE;
CREATE OR REPLACE PROCEDURE AVGMARKS (IN CURSOR IN C_IAMARKS) BEGIN SELECT GREATEST(TEST1,TEST2) AS A, GREATEST(TEST1,TEST3) AS B, GREATEST(TEST3,TEST2) AS C FROM IAMARKS WHERE FINALIA IS NULL;
CREATE TABLE DEPARTMENT
(DNO VARCHAR2 (20) PRIMARY KEY,
DNAME VARCHAR2 (20),
MGRSTARTDATE DATE);
CREATE TABLE DEPARTMENT (DNO VARCHAR2 (20) PRIMARY KEY, DNAME VARCHAR2 (20), MGRSTARTDATE DATE);
USE db1;
CREATE TABLE DEPARTMENT(DNO VARCHAR2(20) PRIMARY KEY,DNAME VARCHAR2(20),MGRSTARTDATE DATE);
CREATE TABLE DEPARTMENT(DNO VARCHAR(20) PRIMARY KEY,DNAME VARCHAR(20),MGRSTARTDATE DATE);
CREATE TABLE EMPLOYEE
(SSN VARCHAR(20) PRIMARY KEY,
FNAME VARCHAR(20),
LNAME VARCHAR(20),
ADDRESS VARCHAR(20),
SEX CHAR(1),
SALARY INTEGER,
FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE (SSN VARCHAR(20) PRIMARY KEY, FNAME VARCHAR(20), LNAME VARCHAR(20), ADDRESS VARCHAR(20), SEX CHAR(1), SALARY INTEGER, FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE
(SSN VARCHAR(20) PRIMARY KEY,
FNAME VARCHAR(20),
LNAME VARCHAR(20),
ADDRESS VARCHAR(20),
SEX CHAR(1),
SALARY INTEGER,
SUPERSSN VARCHAR(20),
DNO VARCHAR(20)
FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE (SSN VARCHAR(20) PRIMARY KEY, FNAME VARCHAR(20), LNAME VARCHAR(20), ADDRESS VARCHAR(20), SEX CHAR(1), SALARY INTEGER, SUPERSSN VARCHAR(20), DNO VARCHAR(20) FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE (SSN VARCHAR(20) PRIMARY KEY, FNAME VARCHAR(20), LNAME VARCHAR(20), ADDRESS VARCHAR(20), SEX CHAR(1), SALARY INTEGER, DNO VARCHAR(20),SUPERSSN VARCHAR(20) REFERENCES SSN, FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE
(SSN VARCHAR(20) PRIMARY KEY,
FNAME VARCHAR(20),
LNAME VARCHAR(20),
ADDRESS VARCHAR(20),
SEX CHAR(1),
SALARY INTEGER,
SUPERSSN VARCHAR(20),
DNO VARCHAR(20)
SUPERSSN VARCHAR(20) REFERENCES EMPLOYEE,
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE (SSN VARCHAR(20) PRIMARY KEY, FNAME VARCHAR(20), LNAME VARCHAR(20), ADDRESS VARCHAR(20), SEX CHAR(1), SALARY INTEGER, SUPERSSN VARCHAR(20), DNO VARCHAR(20) SUPERSSN VARCHAR(20) REFERENCES EMPLOYEE, FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE
(SSN VARCHAR(20) PRIMARY KEY,
FNAME VARCHAR(20),
LNAME VARCHAR(20),
ADDRESS VARCHAR(20),
SEX CHAR(1),
SALARY INTEGER,
SUPERSSN VARCHAR(20),
DNO VARCHAR(20)
SUPERSSN VARCHAR(20) REFERENCES EMPLOYEE,
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE (SSN VARCHAR(20) PRIMARY KEY, FNAME VARCHAR(20), LNAME VARCHAR(20), ADDRESS VARCHAR(20), SEX CHAR(1), SALARY INTEGER, SUPERSSN VARCHAR(20), DNO VARCHAR(20) SUPERSSN VARCHAR(20) REFERENCES EMPLOYEE, FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE
(SSN VARCHAR(20) PRIMARY KEY,
FNAME VARCHAR(20),
LNAME VARCHAR(20),
ADDRESS VARCHAR(20),
SEX CHAR(1),
SALARY INTEGER,
SUPERSSN VARCHAR(20),
DNO VARCHAR(20),
SUPERSSN VARCHAR(20) REFERENCES EMPLOYEE,
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE (SSN VARCHAR(20) PRIMARY KEY, FNAME VARCHAR(20), LNAME VARCHAR(20), ADDRESS VARCHAR(20), SEX CHAR(1), SALARY INTEGER, SUPERSSN VARCHAR(20), DNO VARCHAR(20), SUPERSSN VARCHAR(20) REFERENCES EMPLOYEE, FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
;CREATE TABLE EMPLOYEE
(SSN VARCHAR(20) PRIMARY KEY,
FNAME VARCHAR(20),
LNAME VARCHAR(20),
ADDRESS VARCHAR(20),
SEX CHAR(1),
SALARY INTEGER,
DNO VARCHAR(20),
SUPERSSN VARCHAR(20) REFERENCES EMPLOYEE,
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE (SSN VARCHAR(20) PRIMARY KEY, FNAME VARCHAR(20), LNAME VARCHAR(20), ADDRESS VARCHAR(20), SEX CHAR(1), SALARY INTEGER, DNO VARCHAR(20), SUPERSSN VARCHAR(20) REFERENCES EMPLOYEE, FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE
(SSN VARCHAR(20) PRIMARY KEY,
FNAME VARCHAR(20),
LNAME VARCHAR(20),
ADDRESS VARCHAR(20),
SEX CHAR(1),
SALARY INTEGER,
DNO VARCHAR(20),
SUPERSSN VARCHAR(20) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE EMPLOYEE (SSN VARCHAR(20) PRIMARY KEY, FNAME VARCHAR(20), LNAME VARCHAR(20), ADDRESS VARCHAR(20), SEX CHAR(1), SALARY INTEGER, DNO VARCHAR(20), SUPERSSN VARCHAR(20) REFERENCES EMPLOYEE(SSN), FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
ALTER TABLE DEPARTMENT
ADD MGRSSN VARCHAR(20);
ALTER TABLE DEPARTMENT ADD MGRSSN VARCHAR(20);
ALTER TABLE DEPARTMENT
ADD FOREIGN KEY(MGRSSN) REFERENCES EMPLOYEE(SSN);
ALTER TABLE DEPARTMENT ADD FOREIGN KEY(MGRSSN) REFERENCES EMPLOYEE(SSN);
DESC DEPARTMENT;
CREATE TABLE DLOCATION
(DLOC VARCHAR(20),
DNO VARCHAR(20),
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO),
PRIMARY KEY(DNO, DLOC));
CREATE TABLE DLOCATION (DLOC VARCHAR(20), DNO VARCHAR(20), FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO), PRIMARY KEY(DNO, DLOC));
CREATE TABLE PROJECT
(PNO INTEGER PRIMARY KEY,
PNAME VARCHAR(20),
PLOCATION VARCHAR(20),
DNO VARCHAR(20),
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE PROJECT (PNO INTEGER PRIMARY KEY, PNAME VARCHAR(20), PLOCATION VARCHAR(20), DNO VARCHAR(20), FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));
CREATE TABLE WORKS_ON
(HOURS NUMBER(2),
SSN VARCHAR(20),
PNO VARCHAR(20),
FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY(PNO) REFERENCES PROJECT(PNO),
PRIMARY KEY(SSN, PNO));
CREATE TABLE WORKS_ON (HOURS NUMBER(2), SSN VARCHAR(20), PNO VARCHAR(20), FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY(PNO) REFERENCES PROJECT(PNO), PRIMARY KEY(SSN, PNO));
CREATE TABLE WORKS_ON
(HOURS INT,
SSN VARCHAR(20),
PNO VARCHAR(20),
FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY(PNO) REFERENCES PROJECT(PNO),
PRIMARY KEY(SSN, PNO));
CREATE TABLE WORKS_ON (HOURS INT, SSN VARCHAR(20), PNO VARCHAR(20), FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY(PNO) REFERENCES PROJECT(PNO), PRIMARY KEY(SSN, PNO));
CREATE TABLE WORKS_ON
(HOURS INTEGER,
SSN VARCHAR(20),
PNO INTEGER,
FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY(PNO) REFERENCES PROJECT(PNO),
PRIMARY KEY(SSN, PNO));
CREATE TABLE WORKS_ON (HOURS INTEGER, SSN VARCHAR(20), PNO INTEGER, FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY(PNO) REFERENCES PROJECT(PNO), PRIMARY KEY(SSN, PNO));
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSECE01’,’JOHN’,’SCOTT’,’BANGALORE’,’M’, 450000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES (‘RNSECE01’,’JOHN’,’SCOTT’,’BANGALORE’,’M’, 450000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE01’,’JAMES’,’SMITH’,’BANGALORE’,’M’, 500000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES (‘RNSCSE01’,’JAMES’,’SMITH’,’BANGALORE’,’M’, 500000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE02’,’HEARN’,’BAKER’,’BANGALORE’,’M’, 700000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES (‘RNSCSE02’,’HEARN’,’BAKER’,’BANGALORE’,’M’, 700000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE03’,’EDWARD’,’SCOTT’,’MYSORE’,’M’, 500000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES (‘RNSCSE03’,’EDWARD’,’SCOTT’,’MYSORE’,’M’, 500000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE04’,’PAVAN’,’HEGDE’,’MANGALORE’,’M’, 650000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES (‘RNSCSE04’,’PAVAN’,’HEGDE’,’MANGALORE’,’M’, 650000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE05’,’GIRISH’,’MALYA’,’MYSORE’,’M’, 450000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES (‘RNSCSE05’,’GIRISH’,’MALYA’,’MYSORE’,’M’, 450000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE06’,’NEHA’,’SN’,’BANGALORE’,’F’, 800000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES (‘RNSCSE06’,’NEHA’,’SN’,’BANGALORE’,’F’, 800000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSACC01’,’AHANA’,’K’,’MANGALORE’,’F’, 350000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES (‘RNSACC01’,’AHANA’,’K’,’MANGALORE’,’F’, 350000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSACC02’,’SANTHOSH’,’KUMAR’,’MANGALORE’,’M’, 300000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES (‘RNSACC02’,’SANTHOSH’,’KUMAR’,’MANGALORE’,’M’, 300000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSISE01’,’VEENA’,’M’,’MYSORE’,’M’, 600000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES (‘RNSISE01’,’VEENA’,’M’,’MYSORE’,’M’, 600000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSIT01’,’NAGESH’,’HR’,’BANGALORE’,’M’, 500000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES (‘RNSIT01’,’NAGESH’,’HR’,’BANGALORE’,’M’, 500000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES("RNSECE01","JOHN","SCOTT","BANGALORE","M", 450000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES("RNSCSE01","JAMES","SMITH","BANGALORE","M", 500000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES("RNSCSE02","HEARN","BAKER","BANGALORE","M", 700000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES("RNSCSE03","EDWARD","SCOTT","MYSORE","M", 500000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES("RNSCSE04","PAVAN","HEGDE","MANGALORE","M", 650000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES("RNSCSE05","GIRISH","MALYA","MYSORE","M", 450000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES("RNSCSE06","NEHA","SN","BANGALORE","F", 800000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES("RNSACC01","AHANA","K","MANGALORE","F", 350000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES("RNSACC02","SANTHOSH","KUMAR","MANGALORE","M", 300000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES("RNSISE01","VEENA","M","MYSORE","M", 600000);
INSERT INTO EMPLOYEE(SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES("RNSIT01","NAGESH","HR","BANGALORE","M", 500000);
INSERT INTO DEPARTMENT VALUES("1","ACCOUNTS","01-JAN-01","RNSACC02");
INSERT INTO DEPARTMENT VALUES("2","IT","01-AUG-16","RNSIT01");
INSERT INTO DEPARTMENT VALUES("3","ECE","01-JUN-08","RNSECE01");
INSERT INTO DEPARTMENT VALUES("4","ISE","01-AUG-15","RNSISE01");
INSERT INTO DEPARTMENT VALUES("5","CSE","01-JUN-02","RNSCSE05");INSERT INTO DEPARTMENT VALUES("1","ACCOUNTS","01-01-01","RNSACC02");
INSERT INTO DEPARTMENT VALUES("1","ACCOUNTS","01-01-01","RNSACC02");
SELECT * FROM DEPARTMENT;
INSERT INTO DEPARTMENT VALUES("2","IT","01-08-16","RNSIT01");
INSERT INTO DEPARTMENT VALUES("3","ECE","01-06-08","RNSECE01");
INSERT INTO DEPARTMENT VALUES("4","ISE","01-08-15","RNSISE01");
INSERT INTO DEPARTMENT VALUES("5","CSE","01-06-02","RNSCSE05");
UPDATE EMPLOYEE SET
SUPERSSN=NULL, DNO='3'
WHERE SSN='RNSECE01';
UPDATE EMPLOYEE SET SUPERSSN=NULL, DNO='3' WHERE SSN='RNSECE01';
UPDATE EMPLOYEE SET
SUPERSSN='RNSCSE02', DNO='5'
WHERE SSN='RNSCSE01';
UPDATE EMPLOYEE SET SUPERSSN='RNSCSE02', DNO='5' WHERE SSN='RNSCSE01';
UPDATE EMPLOYEE SET
SUPERSSN='RNSCSE03', DNO='5'
WHERE SSN='RNSCSE02';
UPDATE EMPLOYEE SET SUPERSSN='RNSCSE03', DNO='5' WHERE SSN='RNSCSE02';
UPDATE EMPLOYEE SET
SUPERSSN='RNSCSE04', DNO='5'
WHERE SSN='RNSCSE03';
UPDATE EMPLOYEE SET SUPERSSN='RNSCSE04', DNO='5' WHERE SSN='RNSCSE03';
UPDATE EMPLOYEE SET
DNO='5', SUPERSSN='RNSCSE05'
WHERE SSN='RNSCSE04';
UPDATE EMPLOYEE SET DNO='5', SUPERSSN='RNSCSE05' WHERE SSN='RNSCSE04';
UPDATE EMPLOYEE SET
DNO='5', SUPERSSN='RNSCSE06'
WHERE SSN='RNSCSE05';
UPDATE EMPLOYEE SET DNO='5', SUPERSSN='RNSCSE06' WHERE SSN='RNSCSE05';
UPDATE EMPLOYEE SET
DNO='5', SUPERSSN=NULL
WHERE SSN='RNSCSE06';
UPDATE EMPLOYEE SET DNO='5', SUPERSSN=NULL WHERE SSN='RNSCSE06';
UPDATE EMPLOYEE SET
DNO='1', SUPERSSN='RNSACC02'
WHERE SSN='RNSACC01';
UPDATE EMPLOYEE SET DNO='1', SUPERSSN='RNSACC02' WHERE SSN='RNSACC01';
UPDATE EMPLOYEE SET
DNO='1', SUPERSSN=NULL
WHERE SSN='RNSACC02';
UPDATE EMPLOYEE SET DNO='1', SUPERSSN=NULL WHERE SSN='RNSACC02';
UPDATE EMPLOYEE SET
DNO='4', SUPERSSN=NULL
WHERE SSN='RNSISE01';
UPDATE EMPLOYEE SET DNO='4', SUPERSSN=NULL WHERE SSN='RNSISE01';
UPDATE EMPLOYEE SET
DNO='2', SUPERSSN=NULL
WHERE SSN='RNSIT01';
UPDATE EMPLOYEE SET DNO='2', SUPERSSN=NULL WHERE SSN='RNSIT01';
INSERT INTO DLOCATION VALUES('BANGALORE', '1');
INSERT INTO DLOCATION VALUES('BANGALORE', '2');
INSERT INTO DLOCATION VALUES('BANGALORE', '3');
INSERT INTO DLOCATION VALUES('MANGALORE', '4');
INSERT INTO DLOCATION VALUES('MANGALORE', '5');
INSERT INTO PROJECT VALUES(100,'IOT','BANGALORE','5');
INSERT INTO PROJECT VALUES(101,'CLOUD','BANGALORE','5');
INSERT INTO PROJECT VALUES(102,'BIGDATA','BANGALORE','5');
INSERT INTO PROJECT VALUES(103,'SENSORS','BANGALORE','3');
INSERT INTO PROJECT VALUES(104,'BANK MANAGEMENT','BANGALORE','1');
INSERT INTO PROJECT VALUES(105,'SALARY MANAGEMENT','BANGALORE','1');
INSERT INTO PROJECT VALUES(106,'OPENSTACK','BANGALORE','4');
INSERT INTO PROJECT VALUES(107,'SMART CITY','BANGALORE','2');
INSERT INTO WORKS_ON VALUES(4, 'RNSCSE01', 100);
INSERT INTO WORKS_ON VALUES(6, 'RNSCSE01', 101);
INSERT INTO WORKS_ON VALUES(8, 'RNSCSE01', 102);
INSERT INTO WORKS_ON VALUES(10, 'RNSCSE02', 100);
INSERT INTO WORKS_ON VALUES(3, 'RNSCSE04', 100);
INSERT INTO WORKS_ON VALUES(4, 'RNSCSE05', 101);
INSERT INTO WORKS_ON VALUES(5, 'RNSCSE06', 102);
INSERT INTO WORKS_ON VALUES(6, 'RNSCSE03', 102);
INSERT INTO WORKS_ON VALUES(7, 'RNSECE01', 103);
INSERT INTO WORKS_ON VALUES(5, 'RNSACC01', 104);
INSERT INTO WORKS_ON VALUES(6, 'RNSACC02', 105);
INSERT INTO WORKS_ON VALUES(4, 'RNSISE01', 106);
INSERT INTO WORKS_ON VALUES(10,'RNSITE01', 107);
SELECT * FROM WORKS_ON;
INSERT INTO WORKS_ON VALUES(10,'RNSITE01', 107);
SELECT * FROM PROJECT;
SELECT * FROM DEPARTMENT;
SELECT * FROM EMPLOYEE;
SELECT * FROM DLOCATION;
SELECT * FROM PROJECT;
SELECT * FROM WORKS_ON;
USE db1;
SELECT * FROM DEPARTMENT;
SELECT * FROM EMPLOYEE;
SELECT * FROM DLOCATION;
SELECT * FROM PROJECT;
SELECT * FROM WORKS_ON;
USE db1;
SELECT * FROM EMPLOYEE;
SELECT * FROM DLOCATION;
SELECT * FROM PROJECT;
SELECT * FROM DEPARTMENT;
SELECT * FROM EMPLOYEE;
SELECT * FROM DLOCATION;
SELECT * FROM PROJECT;
SELECT * FROM WORKS_ON;
DESC DEPARTMENT;
DESC EMPLOYEE;
DESC DLOCATION;
DESC PROJECT;
DESC WORKS_ON;
USE db1;
DESC DEPARTMENT;
DESC EMPLOYEE;
DESC DLOCATION;
DESC PROJECT;
DESC WORKS_ON;
SELECT * FROM EMPLOYEE;
SELECT PNO FROM PROJECT WHERE DNO IN (SELECT E.DNO FROM EMPLOYEE E WHERE E.LNAME='SCOTT');
SELECT E.DNO FROM EMPLOYEE E WHERE E.LNAME='SCOTT';
(SELECT DISTINCT P.PNO
FROM PROJECT P, DEPARTMENT D, EMPLOYEE E
WHERE E.DNO=D.DNO
AND D.MGRSSN=E.SSN
AND E.LNAME='SCOTT')
UNION
(SELECT DISTINCT P1.PNO
FROM PROJECT P1, WORKS_ON W, EMPLOYEE E1
WHERE P1.PNO=W.PNO
AND E1.SSN=W.SSN
AND E1.LNAME='SCOTT');
(SELECT DISTINCT P.PNO FROM PROJECT P, DEPARTMENT D, EMPLOYEE E WHERE E.DNO=D.DNO AND D.MGRSSN=E.SSN AND E.LNAME='SCOTT') UNION (SELECT DISTINCT P1.PNO FROM PROJECT P1, WORKS_ON W, EMPLOYEE E1 WHERE P1.PNO=W.PNO AND E1.SSN=W.SSN AND E1.LNAME='SCOTT');
SELECT * FROM DEPARTMENT;
SELECT SSN, FNAME, LNAME, 1.1 * SALARY FROM EMPLOYEE WHERE DNO IN (SELECT DNO FROM PROJECT WHERE PNAME='IOT');
SELECT SSN, FNAME, LNAME, 1.1 * SALARY AS SALARY FROM EMPLOYEE WHERE DNO IN (SELECT DNO FROM PROJECT WHERE PNAME='IOT');
SELECT SUM(SALARY) FROM EMPLOYEE WHERE DNO IN (SELECT DNO FROM DEPARTMENT WHERE DNAME='ACCOUNTS');
SELECT SSN, FNAME, LNAME FROM EMPLOYEE E WHERE NOT EXISTS( (SELECT PNO FROM PROJECT WHERE DNO='5') MINUS (SELECT PNO FROM WORKS_ON WHERE SSN=E.SSN))
;
SELECT SSN, FNAME, LNAME FROM EMPLOYEE E WHERE NOT EXISTS( (SELECT PNO FROM PROJECT WHERE DNO='5') - (SELECT PNO FROM WORKS_ON WHERE SSN=E.SSN));
SELECT SSN, FNAME, LNAME FROM EMPLOYEE E WHERE NOT EXISTS( (SELECT PNO FROM PROJECT WHERE DNO='5') MINUS (SELECT PNO FROM WORKS_ON WHERE SSN=E.SSN));
SELECT SSN, FNAME, LNAME FROM EMPLOYEE E WHERE NOT EXISTS((SELECT PNO FROM PROJECT WHERE DNO=5) MINUS (SELECT PNO FROM WORKS_ON WHERE SSN=E.SSN));
SELECT SSN, FNAME, LNAME FROM EMPLOYEE E WHERE NOT EXISTS((SELECT PNO FROM PROJECT WHERE DNO=5) EXCEPT (SELECT PNO FROM WORKS_ON WHERE SSN=E.SSN));
SELECT DNO, COUNT(SSN) AS NO_EMPLOYEES FROM EMPLOYEE WHERE SALARY>60000 AND DNO IN (SELECT DNO FROM EMPLOYEE WHERE COUNT(SSN) > 5);
SELECT DNO FROM EMPLOYEE WHERE COUNT(SSN) > 5;
SELECT DNO FROM EMPLOYEE E WHERE COUNT(E.SSN) > 5;
SELECT COUNT(SSN) FROM EMPLOYEE;
SELECT SSN, COUNT(SSN) FROM EMPLOYEE GROUP BY SSN;
SELECT SSN, COUNT(SSN) FROM EMPLOYEE GROUP BY DNO;
SELECT DNO, COUNT(SSN) FROM EMPLOYEE GROUP BY DNO;
SELECT DNO, COUNT(SSN) AS NO_EMPLOYEES FROM EMPLOYEE WHERE SALARY>60000 AND DNO IN (SELECT DNO, COUNT(SSN) AS COUNT FROM EMPLOYEE GROUP BY DNO WHERE COUNT>5);
SELECT DNO, COUNT(SSN) FROM EMPLOYEE GROUP BY DNO;
SELECT DNO, COUNT(SSN) AS COUNT FROM EMPLOYEE GROUP BY DNO HAVING COUNT > 5;
SELECT DNO, COUNT(SSN) AS NO_EMPLOYEES FROM EMPLOYEE WHERE SALARY>60000 AND DNO IN (SELECT DNO, COUNT(SSN) AS COUNT FROM EMPLOYEE GROUP BY DNO HAVING COUNT > 5);
SELECT DNO, COUNT(SSN) AS COUNT FROM EMPLOYEE GROUP BY DNO HAVING COUNT > 5;
SELECT SSN FROM EMPLOYEE WHERE DNO IN (SELECT DNO, COUNT(SSN) AS COUNT FROM EMPLOYEE GROUP BY DNO HAVING COUNT > 5);
SELECT SSN FROM EMPLOYEE WHERE DNO IN (SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT(SSN) > 5);
SELECT SSN FROM EMPLOYEE WHERE DNO IN (SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT(SSN) > 5) AND SALARY > 60000;
SELECT SSN, FNAME, LNAME, SALARY FROM EMPLOYEE WHERE DNO IN (SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT(SSN) > 5) AND SALARY > 60000;
SELECT SSN, FNAME, LNAME, SALARY FROM EMPLOYEE WHERE (SALARY > 600000) AND (DNO IN (SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT(SSN) > 5));
SELECT DNO, CNT FROM (SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5);
SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5;
SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING (CNT > 5) AND (SALARY > 600000);
SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5;
WITH TMP(DNO) AS (SELECT DNO FROM EMPLOYEE WHERE SALARY > 600000) SELECT T.DNO, COUNT(E.SSN) AS CNT FROM TMP T, EMPLOYEE E HAVING CNT>5;
WITH TMP(DNO) AS (SELECT DNO FROM EMPLOYEE WHERE SALARY > 600000) SELECT DNO, COUNT(E.SSN) AS CNT FROM TMP T, EMPLOYEE E HAVING CNT>5;
SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE E WHERE E.SALARY>600000 GROUP BY DNO HAVING CNT > 5;
SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE WHERE SALARY>600000 GROUP BY DNO HAVING CNT > 5;
SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE WHERE SALARY>600000;
SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5;
SELECT SSN, SALARY, DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5;
USE DB1;
USE db1;
SELECT SALARY, DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5;
SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5;
SELECT DNO FROM (SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5);
SELECT DNO FROM (SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5) AS 'ABC';
SELECT DNO FROM (SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5);
WITH TMP AS (SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5) SELECT DNO;
WITH TMP AS (SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5) SELECT TMP.DNO;
WITH TMP AS (SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5) SELECT DNO FROM TMP;
WITH TMP AS (SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5) SELECT CNT FROM TMP;
WITH TMP AS (SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5) SELECT DNO, CNT FROM TMP;
WITH TMP AS (SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5) SELECT E.DNO, CNT FROM TMP, EMPLOYEE E WHERE ((E.DNO = TMP.DNO) AND (E.SALARY > 600000));
WITH TMP AS (SELECT DNO, COUNT(SSN) AS CNT FROM EMPLOYEE GROUP BY DNO HAVING CNT > 5) SELECT E.DNO, CNT FROM TMP, EMPLOYEE E WHERE ((E.DNO = TMP.DNO) AND (E.SALARY > 600000)) GROUP BY CNT;
USE db1;
SELECT B.BID, B.TITLE, B.PUBNAME, A.ANAME,C.NOOFCOPIES,L.BRANCHID
FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L
WHERE B.BID=A.BID
AND B.BID=C.BID
AND L.BRANCHID=C.BRANCHID;
SELECT B.BID, B.TITLE, B.PUBNAME, A.ANAME,C.NOOFCOPIES,L.BRANCHID FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L WHERE B.BID=A.BID AND B.BID=C.BID AND L.BRANCHID=C.BRANCHID;
CREATE TABLE PUBLISHER
(NAME VARCHAR(20) PRIMARY KEY,
PHONE INTEGER,
ADDRESS VARCHAR(20));
CREATE TABLE PUBLISHER (NAME VARCHAR(20) PRIMARY KEY, PHONE INTEGER, ADDRESS VARCHAR(20));
CREATE TABLE BOOK
(BID INTEGER PRIMARY KEY,
TITLE VARCHAR(20),
PUBYEAR VARCHAR(20),
PUBNAME REFERENCES PUBLISHER(NAME) ON DELETE CASCADE);
CREATE TABLE BOOK (BID INTEGER PRIMARY KEY, TITLE VARCHAR(20), PUBYEAR VARCHAR(20), PUBNAME REFERENCES PUBLISHER(NAME) ON DELETE CASCADE);
CREATE TABLE BOOK
(BID INTEGER PRIMARY KEY,
TITLE VARCHAR(20),
PUBYEAR VARCHAR(20),
PUBNAME VARCHAR(20),
FOREIGN KEY(PUBNAME) REFERENCES PUBLISHER(NAME) ON DELETE CASCADE);
CREATE TABLE BOOK (BID INTEGER PRIMARY KEY, TITLE VARCHAR(20), PUBYEAR VARCHAR(20), PUBNAME VARCHAR(20), FOREIGN KEY(PUBNAME) REFERENCES PUBLISHER(NAME) ON DELETE CASCADE);
CREATE TABLE BOOK_AUTHORS
(ANAME VARCHAR(20),
BID INTEGER,
FOREIGN KEY(BID) REFERENCES BOOK(BID) ON DELETE CASCADE,
PRIMARY KEY(BID, ANAME));
CREATE TABLE BOOK_AUTHORS (ANAME VARCHAR(20), BID INTEGER, FOREIGN KEY(BID) REFERENCES BOOK(BID) ON DELETE CASCADE, PRIMARY KEY(BID, ANAME));
CREATE TABLE LIBRARY_BRANCH
(BID INTEGER PRIMARY KEY,
BNAME VARCHAR(50),
ADDRESS VARCHAR(50));
CREATE TABLE LIBRARY_BRANCH (BID INTEGER PRIMARY KEY, BNAME VARCHAR(50), ADDRESS VARCHAR(50));
CREATE TABLE BOOK_COPIES
(NOOFCOPIES INTEGER,
BID INTEGER,
BRANCHID INTEGER,
FOREIGN KEY(BID) REFERENCES BOOK(BID) ON DELETE CASCADE,
FOREIGN KEY(BRANCHID) REFERENCES LIBRARY_BRANCH(BID) ON DELETE CASCADE,
PRIMARY KEY (BID,BRANCHID));
CREATE TABLE BOOK_COPIES (NOOFCOPIES INTEGER, BID INTEGER, BRANCHID INTEGER, FOREIGN KEY(BID) REFERENCES BOOK(BID) ON DELETE CASCADE, FOREIGN KEY(BRANCHID) REFERENCES LIBRARY_BRANCH(BID) ON DELETE CASCADE, PRIMARY KEY (BID,BRANCHID));
DESC BOOK_COPIES;
CREATE TABLE CARD
(CARDNO INTEGER PRIMARY KEY);
CREATE TABLE CARD (CARDNO INTEGER PRIMARY KEY);
CREATE TABLE BOOKLENDING
(DATEOUT DATE,
DUEDATE DATE,
BID INTEGER,
BRANCHID INTEGER,
CARDNO INTEGER,
FOREIGN KEY(BID) REFERENCES BOOK(BID) ON DELETE CASCADE,
FOREIGN KEY(BRANCHID) REFERENCES LIBRARY_BRANCH(BRANCHID) ON DELETE CASCADE,
FOREIGN KEY(CARDNO) REFERENCES CARD(CARDNO) ON DELETE CASCADE,
PRIMARY KEY (BID,BRANCHID,CARDNO));
CREATE TABLE BOOKLENDING (DATEOUT DATE, DUEDATE DATE, BID INTEGER, BRANCHID INTEGER, CARDNO INTEGER, FOREIGN KEY(BID) REFERENCES BOOK(BID) ON DELETE CASCADE, FOREIGN KEY(BRANCHID) REFERENCES LIBRARY_BRANCH(BRANCHID) ON DELETE CASCADE, FOREIGN KEY(CARDNO) REFERENCES CARD(CARDNO) ON DELETE CASCADE, PRIMARY KEY (BID,BRANCHID,CARDNO));
CREATE TABLE BOOKLENDING
(DATEOUT DATE,
DUEDATE DATE,
BID INTEGER,
BRANCHID INTEGER,
CARDNO INTEGER,
FOREIGN KEY(BID) REFERENCES BOOK(BID) ON DELETE CASCADE,
FOREIGN KEY(BRANCHID) REFERENCES LIBRARY_BRANCH(BID) ON DELETE CASCADE,
FOREIGN KEY(CARDNO) REFERENCES CARD(CARDNO) ON DELETE CASCADE,
PRIMARY KEY (BID,BRANCHID,CARDNO));
CREATE TABLE BOOKLENDING (DATEOUT DATE, DUEDATE DATE, BID INTEGER, BRANCHID INTEGER, CARDNO INTEGER, FOREIGN KEY(BID) REFERENCES BOOK(BID) ON DELETE CASCADE, FOREIGN KEY(BRANCHID) REFERENCES LIBRARY_BRANCH(BID) ON DELETE CASCADE, FOREIGN KEY(CARDNO) REFERENCES CARD(CARDNO) ON DELETE CASCADE, PRIMARY KEY (BID,BRANCHID,CARDNO));
INSERT INTO PUBLISHER VALUES('MCGRAW-HILL',9191919191,'BANGALORE');
INSERT INTO PUBLISHER VALUES('PEARSON',8181818181,'NEWDELHI');
INSERT INTO PUBLISHER VALUES('RANDOM HOUSE',7171717171,'HYDERABAD');
INSERT INTO PUBLISHER VALUES('LIVRE',6161616161,'CHENNAI');
INSERT INTO PUBLISHER VALUES('PLANETA',5151515151,'BANGALORE');
ALTER TABLE PUBLISHER(PHONE TEXT);
ALTER TABLE PUBLISHER CHANGE PHONE TEXT;
DROP TABLE PUBLISHER;
INSERT INTO PUBLISHER VALUES('MCGRAW-HILL',123,'BANGALORE');
INSERT INTO PUBLISHER VALUES('PEARSON',123,'NEWDELHI');
INSERT INTO PUBLISHER VALUES('RANDOM HOUSE',123,'HYDERABAD');
INSERT INTO PUBLISHER VALUES('LIVRE',123,'CHENNAI');
INSERT INTO PUBLISHER VALUES('PLANETA',123,'BANGALORE');
INSERT INTO BOOK VALUES(1,'DBMS','JAN-2017','MCGRAW-HILL');
INSERT INTO BOOK VALUES(2,'ADBMS','JUN-2016','MCGRAW-HILL');
INSERT INTO BOOK VALUES(3,'CN','SEP-2016','PEARSON');
INSERT INTO BOOK VALUES(4,'CG','SEP-2015','PLANETA');
INSERT INTO BOOK VALUES(5,'OS','MAY-2016','PEARSON');
INSERT INTO BOOK_AUTHORS VALUES('NAVATHE',1);
INSERT INTO BOOK_AUTHORS VALUES('NAVATHE',2);
INSERT INTO BOOK_AUTHORS VALUES('TANENBAUM',3);
INSERT INTO BOOK_AUTHORS VALUES('EDWARD ANGEL',4);
INSERT INTO BOOK_AUTHORS VALUES('GALVIN',5);
INSERT INTO LIBRARY_BRANCH VALUES(10,'RR NAGAR','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES(11,'RNSIT','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES(12,'RAJAJI NAGAR','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES(13,'NITTE','MANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES(14,'MANIPAL','UDUPI');
INSERT INTO BOOK_COPIES VALUES(10,1,10);
INSERT INTO BOOK_COPIES VALUES(5,1,11);
INSERT INTO BOOK_COPIES VALUES(2,2,12);
INSERT INTO BOOK_COPIES VALUES(5,2,13);
INSERT INTO BOOK_COPIES VALUES(7,3,14);
INSERT INTO BOOK_COPIES VALUES(1,5,10);
INSERT INTO BOOK_COPIES VALUES(3,4,11);
INSERT INTO CARD VALUES(100);
INSERT INTO CARD VALUES(101);
INSERT INTO CARD VALUES(102);
INSERT INTO CARD VALUES(103);
INSERT INTO CARD VALUES(104);
INSERT INTO BOOKLENDING VALUES('01-JAN-17','01-JUN-17',1,10,101);
INSERT INTO BOOKLENDING VALUES('11-JAN-17','11-MAR-17',3,14,101);
INSERT INTO BOOKLENDING VALUES('21-FEB-17','21-APR-17',2,13,101);
INSERT INTO BOOKLENDING VALUES('15-MAR-17','15-JUL-17',4,11,101);
INSERT INTO BOOKLENDING VALUES('12-APR-17','12-MAY-17',1,11,104);
INSERT INTO BOOKLENDING VALUES('2020-01-01','2020-06-01',1,10,101);
INSERT INTO BOOKLENDING VALUES('2020-01-11','2020-07-11',3,14,101);
INSERT INTO BOOKLENDING VALUES('2020-02-21','2020-08-21',2,13,101);
INSERT INTO BOOKLENDING VALUES('2020-03-15','2020-09-15',4,11,101);
INSERT INTO BOOKLENDING VALUES('2020-04-12','2020-10-12',1,11,104);
USE db1;
SELECT B.BID, B.TITLE, B.PUBNAME, A.ANAME,C.NOOFCOPIES,L.BID FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L WHERE B.BID=A.BID AND B.BID=C.BID AND L.BID=C.BRANCHID;
SELECT CARDNO FROM BOOKLENDING WHERE DATEOUT BETWEEN '2020-01-01' AND '2020-06-01' GROUP BY CARDNO HAVING COUNT(*)>3;
SELECT * FROM BOOKLENDING;
INSERT INTO BOOKLENDING VALUES('2020-01-02','2020-06-01',1,11,101);
INSERT INTO BOOKLENDING VALUES('2020-01-12','2020-07-11',3,15,101);
INSERT INTO BOOKLENDING VALUES('2020-02-22','2020-08-21',2,14,101);
INSERT INTO BOOKLENDING VALUES('2020-03-16','2020-09-15',4,12,101);
INSERT INTO BOOKLENDING VALUES('2020-04-13','2020-10-12',1,12,104);
SELECT CARDNO FROM BOOKLENDING WHERE DATEOUT BETWEEN '2020-01-01' AND '2020-06-01' GROUP BY CARDNO HAVING COUNT(*)>3;
SELECT B.BID, B.TITLE, B.PUBNAME, A.ANAME,C.NOOFCOPIES,L.BID FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L WHERE B.BID=A.BID AND B.BID=C.BID AND L.BID=C.BRANCHID;
SELECT CARDNO FROM BOOKLENDING WHERE DATEOUT BETWEEN '2020-01-01' AND '2020-06-01' GROUP BY CARDNO HAVING COUNT(*)>3;
USE db1;
CREATE TABLE ROUTE(ROUTE_ID INT, START TEXT, END TEXT);
CREATE TABLE DRIVER(DRIVER_ID INT, NAME TEXT, PHONE TEXT, ROUTE_ID INT, FOREIGN KEY(ROUTE_ID) REFERENCES ROUTE(ROUTE_ID));
CREATE TABLE DRIVER(DRIVER_ID INT, NAME TEXT, PHONE TEXT, ROUTE_ID INT, FOREIGN KEY (ROUTE_ID) REFERENCES ROUTE(ROUTE_ID));
DROP TABLE DRIVER;
CREATE TABLE DRIVER(DRIVER_ID INT, NAME TEXT, PHONE TEXT, ROUTE_ID INT, FOREIGN KEY (ROUTE_ID) REFERENCES ROUTE(ROUTE_ID));
DROP TABLE ROUTE;
CREATE TABLE ROUTE(ROUTE_ID INT PRIMARY KEY, START TEXT, END TEXT);
CREATE TABLE DRIVER(DRIVER_ID INT PRIMARY KEY, NAME TEXT, PHONE TEXT, ROUTE_ID INT, FOREIGN KEY (ROUTE_ID)REFERENCES ROUTE(ROUTE_ID));
CREATE TABLE BUS(BUS_NUMBER VARCHAR(10) PRIMARY KEY, MODEL TEXT, DRIVER_ID INT, FOREIGN KEY (DRIVER_ID)REFERENCES DRIVER(DRIVER_ID));
CREATE TABLE RESERVATION(RESERVATION_ID INT PRIMARY KEY, START TEXT, END TEXT, BUS_NUMBER VARCHAR(10), FOREIGN KEY (BUS_NUMBER)REFERENCES BUS(BUS_NUMBER));
INSERT INTO ROUTE VALUES(100, "LONDON", "PARIS");
INSERT INTO ROUTE VALUES(101, "DELHI", "MUMBAI");
INSERT INTO ROUTE VALUES(102, "BMSIT", "YELAHANKA");
INSERT INTO ROUTE VALUES(103, "YELAHANKA", "KEMPEGOWDA");
INSERT INTO DRIVER VALUES(1, "DRIVER1", "1234567890", 100);
INSERT INTO DRIVER VALUES(2, "DRIVER2", "9876532140", 100);
INSERT INTO DRIVER VALUES(3, "DRIVER3", "1234567890", 101);
INSERT INTO DRIVER VALUES(4, "DRIVER4", "1234567890", 102);
INSERT INTO DRIVER VALUES(5, "DRIVER5", "9999999999", 102);
INSERT INTO DRIVER VALUES(6, "DRIVER6", "1234567890", 102);
INSERT INTO DRIVER VALUES(7, "DRIVER7", "1111111111", 103);
INSERT INTO DRIVER VALUES(8, "DRIVER8", "2222222222", 103);
INSERT INTO BUS VALUES("EUR101", "VOLVO", 1);
INSERT INTO BUS VALUES("EUR102", "VOLVO", 2);
INSERT INTO BUS VALUES("IND101", "CRUISE", 3);
INSERT INTO BUS VALUES("BLR101", "BMTC", 4);
INSERT INTO BUS VALUES("BLR102", "BMTC", 5);
INSERT INTO BUS VALUES("BLR103", "BMTC", 6);
INSERT INTO BUS VALUES("BLR104", "BMTC", 7);
INSERT INTO BUS VALUES("BLR105", "BMTC", 8);
INSERT INTO RESERVATION VALUES(1002, "LONDON", "BUDAPEST", 100);
INSERT INTO RESERVATION VALUES(1002, "LONDON", "BUDAPEST", "EUR101");
INSERT INTO RESERVATION VALUES(1001, "DELHI", "THANE", "IND101");
INSERT INTO RESERVATION VALUES(1003, "BMSIT", "PUTTENAHALLI", "BLR101");
INSERT INTO RESERVATION VALUES(1004, "YELAHANKA", "HEBBAL", "BLR105");
DELETE FROM ROUTE;
DELETE FROM RESERVATION;
DELETE FROM BUS;
DELETE FROM DRIVER;
DELETE FROM ROUTE;
INSERT INTO ROUTE VALUES(100, "LONDON", "PARIS");
INSERT INTO ROUTE VALUES(101, "BLR", "DELHI");
INSERT INTO ROUTE VALUES(102, "BMSIT", "YELAHANKA");
INSERT INTO ROUTE VALUES(103, "YELAHANKA", "KEMPEGOWDA");
INSERT INTO DRIVER VALUES(1, "DRIVER1", "1234567890", 100);
INSERT INTO DRIVER VALUES(2, "DRIVER2", "9876532140", 100);
INSERT INTO DRIVER VALUES(3, "DRIVER3", "1234567890", 101);
INSERT INTO DRIVER VALUES(4, "DRIVER4", "1234567890", 102);
INSERT INTO DRIVER VALUES(5, "DRIVER5", "9999999999", 102);
INSERT INTO DRIVER VALUES(6, "DRIVER6", "1234567890", 102);
INSERT INTO DRIVER VALUES(7, "DRIVER7", "1111111111", 103);
INSERT INTO DRIVER VALUES(8, "DRIVER8", "2222222222", 103);
INSERT INTO BUS VALUES("EUR101", "VOLVO", 1);
INSERT INTO BUS VALUES("EUR102", "VOLVO", 2);
INSERT INTO BUS VALUES("IND101", "SLEEPER", 3);
INSERT INTO BUS VALUES("BLR101", "SEATER", 4);
INSERT INTO BUS VALUES("BLR102", "SEATER", 5);
INSERT INTO BUS VALUES("BLR103", "SEATER", 6);
INSERT INTO BUS VALUES("BLR104", "SEATER", 7);
INSERT INTO BUS VALUES("BLR105", "SEATER", 8);
INSERT INTO RESERVATION VALUES(1002, "LONDON", "PARIS", "EUR101");
INSERT INTO RESERVATION VALUES(1001, "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1003, "BMSIT", "YELAHANKA", "BLR101");
INSERT INTO RESERVATION VALUES(1004, "YELAHANKA", "KEMPEGOWDA", "BLR105");
SELECT COUNT(RESERVATION_ID) FROM RESERVATION WHERE START="BLR" AND END="DELHI";
DELETE FROM RESERVATION;
INSERT INTO RESERVATION VALUES(1002, "2019-04-08", "LONDON", "PARIS", "EUR101");
INSERT INTO RESERVATION VALUES(1001, "2019-05-01", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1001, "2019-08-09", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1003, "2019-06-02", "BMSIT", "YELAHANKA", "BLR101");
INSERT INTO RESERVATION VALUES(1004, "2019-07-03", "YELAHANKA", "KEMPEGOWDA", "BLR105");
DROP TABLE RESERVATION;
CREATE TABLE RESERVATION(RESERVATION_ID INT PRIMARY KEY, DATE DATE,START TEXT, END TEXT, BUS_NUMBER VARCHAR(10), FOREIGN KEY (BUS_NUMBER)REFERENCES BUS(BUS_NUMBER));
INSERT INTO RESERVATION VALUES(1004, "2019-07-03", "YELAHANKA", "KEMPEGOWDA", "BLR105");
INSERT INTO RESERVATION VALUES(1002, "2019-04-08", "LONDON", "PARIS", "EUR101");
INSERT INTO RESERVATION VALUES(1001, "2019-05-01", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1001, "2019-08-09", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1003, "2019-06-02", "BMSIT", "YELAHANKA", "BLR101");
INSERT INTO RESERVATION VALUES(1005, "2019-08-09", "BLR", "DELHI", "IND101");
SELECT COUNT(RESERVATION_ID) FROM RESERVATION WHERE START="BLR" AND END="DELHI";
INSERT INTO RESERVATION VALUES(1006, "2019-01-02", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1007, "2019-03-19", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1008, "2019-05-11", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1009, "2019-11-09", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1010, "2019-12-01", "BLR", "DELHI", "IND101");
SELECT COUNT(RESERVATION_ID) FROM RESERVATION WHERE START="BLR" AND END="DELHI";
DELETE FROM RESERVATION;
INSERT INTO RESERVATION VALUES(1002, "2019-04-08", "LONDON", "PARIS", "EUR101");
INSERT INTO RESERVATION VALUES(1003, "2019-06-02", "BMSIT", "YELAHANKA", "BLR101");
INSERT INTO RESERVATION VALUES(1004, "2019-07-03", "YELAHANKA", "KEMPEGOWDA", "BLR105");
INSERT INTO RESERVATION VALUES(1001, "2019-05-01", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1005, "2019-08-09", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1006, "2020-12-23", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1007, "2020-12-23", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1008, "2020-12-23", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1009, "2020-12-23", "BLR", "DELHI", "IND101");
INSERT INTO RESERVATION VALUES(1010, "2020-12-23", "BLR", "DELHI", "IND101");
SELECT COUNT(RESERVATION_ID) FROM RESERVATION WHERE START="BLR" AND END="DELHI";
SELECT COUNT(RESERVATION_ID) FROM RESERVATION WHERE START="BLR" AND END="DELHI" AND DATE="2020-12-23";
USE db1;
SELECT COUNT(R.RESERVATION_ID), R.BUS_NUMBER AS NUMBER_OF_PASSENGERS, B.MODEL FROM RESERVATION R, BUS B WHERE R.START="BLR" AND R.END="DELHI" AND R.DATE="2020-12-23";
SELECT COUNT(R.RESERVATION_ID) AS NUMBER_OF_PASSENGERS, R.BUS_NUMBER, B.MODEL FROM RESERVATION R, BUS B WHERE R.START="BLR" AND R.END="DELHI" AND R.DATE="2020-12-23";
SELECT COUNT(R.RESERVATION_ID) AS NUMBER_OF_PASSENGERS, R.BUS_NUMBER FROM RESERVATION R WHERE R.START="BLR" AND R.END="DELHI" AND R.DATE="2020-12-23";
SELECT COUNT(R.RESERVATION_ID) AS NUMBER_OF_PASSENGERS, R.BUS_NUMBER FROM RESERVATION R WHERE R.START="BLR" AND R.END="DELHI" AND R.DATE="2020-12-23" GROUP BY R.BUS_NUMBER;
SELECT COUNT(R.RESERVATION_ID) AS NUMBER_OF_PASSENGERS, 
R.BUS_NUMBER,
B.MODEL
FROM RESERVATION R, BUS B
WHERE R.START="BLR" AND 
R.END="DELHI" AND 
R.DATE="2020-12-23" AND
B.BUS_NUMBER=R.BUS_NUMBER
GROUP BY R.BUS_NUMBER;
SELECT COUNT(R.RESERVATION_ID) AS NUMBER_OF_PASSENGERS,  R.BUS_NUMBER, B.MODEL FROM RESERVATION R, BUS B WHERE R.START="BLR" AND  R.END="DELHI" AND  R.DATE="2020-12-23" AND B.BUS_NUMBER=R.BUS_NUMBER GROUP BY R.BUS_NUMBER;
DESC ROUTE;
DESC DRIVER;
DESC BUS;
DESC RESERVATION;
use db1;
SELECT D.DNO, COUNT(*) FROM DEPARTMENT D, EMPLOYEE E WHERE D.DNO=E.DNO
AND E.SALARY>600000
AND D.DNO IN(SELECT E1.DNO
FROM EMPLOYEE E1
GROUP BY E1.DNO
HAVING COUNT(*)>5)
GROUP BY D.DNO;
SELECT D.DNO, COUNT(*) FROM DEPARTMENT D, EMPLOYEE E WHERE D.DNO=E.DNO AND E.SALARY>600000 AND D.DNO IN(SELECT E1.DNO FROM EMPLOYEE E1 GROUP BY E1.DNO HAVING COUNT(*)>5) GROUP BY D.DNO;
WITH TMP AS (
SELECT DNO, 
T(SSN) AS CNT 
FROM EMPLOYEE 
GROUP BY DNO 
HAVING CNT > 5
)
SELECT E.DNO, 
CNT 
FROM TMP, 
EMPLOYEE E 
WHERE ((E.DNO = TMP.DNO) AND (E.SALARY > 600000))
GROUP BY CNT;
WITH TMP AS ( SELECT DNO,  T(SSN) AS CNT  FROM EMPLOYEE  GROUP BY DNO  HAVING CNT > 5 ) SELECT E.DNO,  CNT  FROM TMP,  EMPLOYEE E  WHERE ((E.DNO = TMP.DNO) AND (E.SALARY > 600000)) GROUP BY CNT;
WITH TMP AS (
SELECT DNO,COUNT(SSN) AS CNT 
FROM EMPLOYEE 
GROUP BY DNO 
HAVING CNT > 5
)
SELECT E.DNO, 
CNT 
FROM TMP, 
EMPLOYEE E 
WHERE ((E.DNO = TMP.DNO) AND (E.SALARY > 600000))
GROUP BY CNT;
WITH TMP AS ( SELECT DNO,COUNT(SSN) AS CNT  FROM EMPLOYEE  GROUP BY DNO  HAVING CNT > 5 ) SELECT E.DNO,  CNT  FROM TMP,  EMPLOYEE E  WHERE ((E.DNO = TMP.DNO) AND (E.SALARY > 600000)) GROUP BY CNT;
select * from department
;
select * from DEPARTMENT;
select * from EMPLOYEE;
WITH TMP AS (
SELECT DNO,COUNT(SSN) AS CNT 
FROM EMPLOYEE 
GROUP BY DNO 
HAVING CNT > 5
)
SELECT E.DNO, 
CNT 
FROM TMP, 
EMPLOYEE E 
WHERE ((E.DNO = TMP.DNO) AND (E.SALARY > 600000))
GROUP BY CNT;
WITH TMP AS ( SELECT DNO,COUNT(SSN) AS CNT  FROM EMPLOYEE  GROUP BY DNO  HAVING CNT > 5 ) SELECT E.DNO,  CNT  FROM TMP,  EMPLOYEE E  WHERE ((E.DNO = TMP.DNO) AND (E.SALARY > 600000)) GROUP BY CNT;
select SSN from EMPLOYEE E where E.SALARY > 600000;
INSERT INTO EMPLOYEE VALUES("ssn0","name0","title0","place","M", 800000, 2, NULL);
select SSN from EMPLOYEE E where E.SALARY > 600000;
select SSN, DNO from EMPLOYEE E where E.SALARY > 600000;
select SSN, DNO from EMPLOYEE E where E.SALARY > 600000 GROUP BY DNO;
select SSN, count(DNO) from EMPLOYEE E where E.SALARY > 600000 GROUP BY DNO;
select DNO, count(DNO) from EMPLOYEE E where E.SALARY > 600000 GROUP BY DNO;
select DNO, count(DNO) as EMP_COUNT from EMPLOYEE E where E.SALARY > 600000 GROUP BY DNO;
select * from PROJECTl
GRANT ALL PRIVILEGES ON *.* TO 't'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 't'@'localhost'
;
create database db1;
create database db1
;