Skip to content

Instantly share code, notes, and snippets.

@arikchakma

arikchakma/dbms-assignment.sql

Last active April 25, 2025 19:54
Show Gist options
  • Save arikchakma/9e3c328837d11fe14c526c31fd3ef2e4 to your computer and use it in GitHub Desktop.
Save arikchakma/9e3c328837d11fe14c526c31fd3ef2e4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
dbms-assignment.sql
-- a. CREATE EMPLOYEE table
CREATE TABLE
EMPLOYEE (
emp_id NUMBER (11, 0) NOT NULL,
fname VARCHAR(40),
lname VARCHAR(40),
stor_id NUMBER (11, 0),
job_id NUMBER (11, 0),
pub_id NUMBER (11, 0),
hire_date DATE,
job_lvl INTEGER,
city VARCHAR(45),
state VARCHAR(45),
zip VARCHAR(10),
country VARCHAR(45),
managerID NUMBER (11, 0),
supervisorID NUMBER (11, 0),
CONSTRAINT EMPLOYEE_PK PRIMARY KEY (emp_id),
CONSTRAINT EMPLOYEE_FK1 FOREIGN KEY (stor_id) REFERENCES STORE (stor_id),
CONSTRAINT EMPLOYEE_FK2 FOREIGN KEY (pub_id) REFERENCES PUBLISHER (pub_id),
CONSTRAINT EMPLOYEE_FK3 FOREIGN KEY (country) REFERENCES COUNTRY (country)
);
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_FK4 FOREIGN KEY (managerID) REFERENCES EMPLOYEE (emp_id);
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_FK5 FOREIGN KEY (supervisorID) REFERENCES EMPLOYEE (emp_id);
-- b. Change the discount rate to 25% (any discount with 25%) for all sale records related to the titlename “Flight runner” that were sold after 31st March 2019.
UPDATE SALE AS S
JOIN TITLES AS T ON S.title_id = T.title_id
JOIN DISCOUNT AS D ON S.discount_id = D.discount_id
SET
D.discount = 25
WHERE
T.titlename = 'Flight runner'
AND S.saledate > '2019-03-31';
-- c. Display the employee name and their managers’ name (in case of Publishers) or supervisor’s name (in case of store).
SELECT
E.fname as employee_name,
M.fname as manager_name
FROM
EMPLOYEE as E,
EMPLOYEE as M
WHERE
E.managerID = M.emp_id
AND E.pub_id IS NOT NULL
UNION
SELECT
E.fname as employee_name,
S.fname as supervisor_name
FROM
EMPLOYEE as E,
EMPLOYEE as S
WHERE
E.supervisorID = S.emp_id
AND E.stor_id IS NOT NULL;
-- d. Find total number of native employees (employees working at the country from where they belong to) stored in the database. TIT
SELECT
COUNT(*) as total_native_employees
FROM
(
SELECT
E.fname
FROM
EMPLOYEE as E,
PUBLISHER as P,
COUNTRY as C
WHERE
E.pub_id = P.pub_id
AND P.country = C.country
AND E.country = C.country
UNION
SELECT
E.fname
FROM
EMPLOYEE as E,
STORE as S,
COUNTRY as C
WHERE
E.stor_id = S.stor_id
AND S.country = C.country
AND E.country = C.country
) AS native_employees;
-- e. Display continent wise (continent of the employee) no of employees working for any publishing house.
SELECT
C.continent,
COUNT(*) as total_employees
FROM
EMPLOYEE as E,
PUBLISHER as P,
COUNTRY as C
WHERE
E.pub_id IS NOT NULL
AND E.country = C.country
GROUP BY
C.continent;
-- f. Display all the types where total number of books sold of that type is more than 20000.
SELECT
T.type
FROM
TITLES as T,
SALE as S
WHERE
T.title_id = S.title_id
GROUP BY
T.type
HAVING
SUM(S.quantity) > 20000;
-- g. Show the name of the authors who are writing for more than 25 years considering that they have recent publication in 2019.
SELECT
A.au_fname,
A.au_lname
FROM
AUTHOR as A,
TITLEAUTHOR as TA,
TITLES as T
WHERE
A.au_id = TA.au_id
AND TA.title_id = T.title_id
AND T.pub_date >= '2019-01-01'
AND A.au_id IN (
SELECT
A.au_id
FROM
AUTHOR as A,
TITLEAUTHOR as TA,
TITLES as T
WHERE
A.au_id = TA.au_id
AND TA.title_id = T.title_id
AND T.pub_date < '2000-01-01'
);
-- h. Display Store wise total books sold in 2019 for Indian stores.
SELECT
S.stor_name,
SUM(SA.quantity) as total_books_sold
FROM
STORE as S,
SALE as SA
WHERE
S.stor_id = SA.stor_id
AND S.country = 'India'
AND SA.saledate >= '2019-01-01'
GROUP BY
S.stor_name;
-- i. Find the author who has earned maximum revenue in 2019.
SELECT
A.au_fname,
A.au_lname
FROM
AUTHOR as A,
TITLEAUTHOR as TA,
TITLES as T,
SALE as SA
WHERE
A.au_id = TA.au_id
AND TA.title_id = T.title_id
AND T.title_id = SA.title_id
AND YEAR (SA.saledate) = 2019
GROUP BY
A.au_fname,
A.au_lname
ORDER BY
SUM(SA.quantity * T.price) DESC
LIMIT
1;
-- j. List the names of the publishers who are publishing for more than 30 years.
SELECT
P.pub_name
FROM
PUBLISHER as P,
TITLES as T
WHERE
P.pub_id = T.pub_id
AND YEAR (T.pub_date) < 1995;
-- k. Display continent wise total number of authors who have published at least 3 titles.
SELECT
C.continent,
COUNT(*) AS total_authors
FROM
AUTHOR AS A
JOIN COUNTRY AS C ON A.country = C.country
JOIN (
SELECT
TA.au_id
FROM
TITLEAUTHOR AS TA
GROUP BY
TA.au_id
HAVING
COUNT(DISTINCT TA.title_id) >= 1
) AS QualifiedAuthors ON A.au_id = QualifiedAuthors.au_id
GROUP BY
C.continent;
-- l. Find job wise total number of employees.
SELECT
J.job_id,
COUNT(*) as total_employees
FROM
JOB as J,
EMPLOYEE as E
WHERE
J.job_id = E.job_id
GROUP BY
J.job_id;
-- m. Display discount wise total no of orders.
SELECT
D.discount_id,
COUNT(*) as total_orders
FROM
DISCOUNT as D,
SALE as S
WHERE
D.discount_id = S.discount_id
GROUP BY
D.discount_id;
-- n. Display total sell of all the stores with discount over 5%.
SELECT
S.stor_name,
SUM(SA.quantity * T.price) as total_sell
FROM
STORE as S,
SALE as SA,
TITLES as T,
DISCOUNT as D
WHERE
S.stor_id = SA.stor_id
AND SA.title_id = T.title_id
AND SA.discount_id = D.discount_id
AND D.discount > 5
GROUP BY
S.stor_name;
-- o. Display type wise total, average and maximum price of the titles. Display total number of orders issued for ‘business’ type book.
SELECT
T.type,
SUM(T.price) as total_price,
AVG(T.price) as average_price,
MAX(T.price) as maximum_price
FROM
TITLES as T
GROUP BY
T.type;
SELECT
COUNT(*) as total_orders
FROM
SALE as SA,
TITLES as T
WHERE
SA.title_id = T.title_id
AND T.type = 'business';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment