Scribd
Upload
21 views3 pages

Final SQL

The document contains SQL commands for creating and manipulating two tables, AUTHORS and BOOKS, including inserting data and various select queries. It also includes commands for updating employee salaries, creating a view for managers, and selecting data based on specific conditions. The queries involve filtering authors and books based on attributes like publication year, genre, and employee details.

Uploaded by

Abhinav Mishra
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
21 views3 pages

Final SQL

The document contains SQL commands for creating and manipulating two tables, AUTHORS and BOOKS, including inserting data and various select queries. It also includes commands for updating employee salaries, creating a view for managers, and selecting data based on specific conditions. The queries involve filtering authors and books based on attributes like publication year, genre, and employee details.

Uploaded by

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

CREATE TABLE AUTHORS(

AUTHORID NUMBER(2) PRIMARY KEY,


FIRSTNAME VARCHAR(15),
LASTNAME VARCHAR(15),
BIRTHYEAR NUMBER(4),
NATIONALITY VARCHAR(15));

INSERT INTO AUTHORS


VALUES(&AUTHORID,'&FIRSTNAME','&LASTNAME',&BIRTHYEAR,'&NATIONALITY');

CREATE TABLE BOOKS(


BOOKID NUMBER(2) PRIMARY KEY,
TITLE VARCHAR(15),
PUBLICATIONYEAR NUMBER(4),
GENRE VARCHAR(15),
AUTHORID NUMBER(2) REFERENCES AUTHORS (AUTHORID));

INSERT INTO BOOKS VALUES(&BOOKID,'&TITLE',&PUBLICATIONYEAR,'&GENRE',&AUTHORID);

1)
SELECT TITLE FROM BOOKS
WHERE AUTHORID IN(
SELECT AUTHORID FROM AUTHORS
WHERE SUBSTR(FIRSTNAME,-1) IN ('A','E','I','O','U'));

2)
SELECT [Link],[Link],[Link],[Link]
FROM AUTHORS A, BOOKS B
WHERE [Link]=[Link] AND
[Link]>(SELECT PUBLICATIONYEAR FROM BOOKS
WHERE TITLE = 'EMMA') AND
LENGTH(LASTNAME)=5;

3)
SELECT * FROM(
SELECT ROWNUM AS SLNO,BOOKS.* FROM BOOKS)
WHERE SLNO IN (4,7);

4)
SELECT [Link],[Link],[Link]
FROM AUTHORS A ,BOOKS B
WHERE [Link]=[Link] AND
[Link] IN ('ROMANCE','FICTION') AND
[Link] LIKE '__M%';

5)
SELECT GENRE FROM BOOKS
WHERE AUTHORID IN(
SELECT AUTHORID FROM AUTHORS
WHERE BIRTHYEAR >
(SELECT BIRTHYEAR FROM AUTHORS
WHERE FIRSTNAME='MARK'));

6)
SELECT SUBSTR(GENRE,1,3) FROM BOOKS;

7)
SELECT [Link],[Link],[Link] ,[Link]
FROM EMP E1,EMP E2,DEPT D1,DEPT D2
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link]>ALL(SELECT SAL FROM EMP WHERE JOB = 'SALESMAN') AND
[Link] ='ACCOUNTING';

8)
SELECT [Link], [Link]
FROM EMP E , DEPT D
WHERE [Link] = [Link] AND
[Link] IS NOT NULL AND
[Link] IN(SELECT EMPNO FROM EMP WHERE ENAME='BLAKE');

9)
SELECT DNAME, LOC FROM DEPT WHERE DEPTNO IN
(SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>=5);

10)
SELECT DNAME FROM DEPT WHERE DEPTNO IN(
SELECT DEPTNO FROM EMP WHERE SAL IN (
SELECT SAL FROM EMP GROUP BY SAL HAVING COUNT(*)>1));

11)
SELECT SAL FROM(
SELECT ROWNUM AS SLNO,SAL FROM
(SELECT DISTINCT SAL FROM EMP ORDER BY SAL DESC))
WHERE SLNO=10;

12)
UPDATE EMPS
SET SAL = 8000
WHERE JOB ='SALESMAN';

13)
CREATE VIEW MANAGERVIEW
AS
SELECT * FROM EMP
WHERE JOB = 'MANAGER';

14)
SELECT [Link], [Link], [Link], [Link], [Link]
FROM EMP E1, EMP E2, DEPT D1, DEPT D2
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link]<(SELECT SAL FROM EMP WHERE ENAME = 'JONES') AND
[Link] IN ('NEW YORK' , 'CHICAGO');

15)
SELECT [Link], [Link], [Link], [Link], [Link]
FROM EMP E1, EMP E2, DEPT D1, DEPT D2
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] AND
[Link]>(SELECT HIREDATE FROM EMP WHERE ENAME = 'SMITH') AND
[Link] IN ('ACCOUNTING','RESEARCH');

You might also like