SQL Practicals Cs

Practical 16 – EMP-DEPT
USE DATABASE yourdbname

CREATE TABLE DEPT(
deptno INT(2) PRIMARY KEY,
dname VARCHAR(10) NOT NULL,
location VARCHAR(10) NOT NULL);
SET FOREIGN_KEY_CHECKS = 0
CREATE TABLE emp(

empno INT(4) PRIMARY KEY,
ename VARCHAR(10) NOT NULL,
deptno INT(2) NOT NULL,
gender CHAR(1) NOT NULL,
salary INT(5) NOT NULL,
bonus INT(3) NULL,
CONSTRAINT FOREIGN KEY(deptno) REFERENCES dept(deptno)
)Engine='INNODB';
INSERT INTO dept VALUES(10,'ADM','DELHI');

INSERT INTO dept VALUES(11,'FIN','MUMBAI');
INSERT INTO dept VALUES(12,'HR','DELHI');
INSERT INTO dept VALUES(13,'TECH','CHENNAI');
INSERT INTO emp VALUES(1001, 'AMAY', 10, 'M', 8000, 500);

INSERT INTO emp VALUES(1002, 'SUDHA', 11, 'F', 9000, NULL);
INSERT INTO emp VALUES(1003, 'RAVI', 10, 'M', 6000, NULL);
INSERT INTO emp VALUES(1004, 'SUJAY', 12, 'M', 8000, 500);
INSERT INTO emp VALUES(1005, 'JEMMI', 13, 'F', 7000, NULL);
INSERT INTO emp VALUES(1006, 'REKHA', 11, 'F', 7000, 500);
Q1. Write a query to display ename, dname, salary and location for all employees who are not
getting bonus
SELECT ename, dname, salary, location FROM emp, dept WHERE emp.deptno=dept.deptno AND
bonus IS NULL;
Q2. Write a query to display ename, deptno, dname, salary for all employees whose location is
DELHI.
SELECT ename, emp.deptno, dname, salary FROM emp, dept WHERE emp.deptno=dept.deptno
AND location = 'DELHI';
Q3. Write a query to display gender, and number of employees in each gender.
SELECT gender, COUNT(*) FROM emp GROUP BY gender;
Q5. Write a query to display dname, total salary in each dname.
SELECT dname, SUM(salary) FROM emp, dept WHERE emp.deptno=dept.deptno GROUP BY

dname;
Practical 17 – Hospital
USE yourdbname
CREATE TABLE hospital(

pid INT(4) PRIMARY KEY,
pname VARCHAR(10) NOT NULL,
ward VARCHAR(15) NOT NULL,
days INT(3) NOT NULL,
bill INT(5) NOT NULL,
discount INT(3) NULL
);
INSERT INTO hospital VALUES(1001, 'Rohit', 'Medicine', 15,10000, NULL);

INSERT INTO hospital VALUES(1002, 'Sumana', 'Surgery', , 10, 8000, 500);
INSERT INTO hospital VALUES(1003, 'Gagan', 'Medicine', 15, 7000, NULL);
INSERT INTO hospital VALUES(1004, 'Sapna', 'Neuro', 20, 15000, 1000);
INSERT INTO hospital VALUES(1005, 'Arti', 'Medicine', 15, 5000, 500);
INSERT INTO hospital VALUES(1006, 'Sumit', 'Surgery', 10, 8000, NULL);
Q1. Write a query to display pname, ward and bill for all the patients who are getting some discount.
SELECT pname, ward, bill FROM hospital WHERE discount IS NOT NULL;
Q2. Write a query to display, pname, ward and days and bill for all patients who are paying bill in the
range 8000 to 10000
SELECT pname, ward, days FROM hospital WHERE bill BETWEEN 8000 AND 10000;
Q3. Write a query to display pname, ward, days and bill for all the patients whose days are either 10
of 15
SELECT pname, ward, days, bill FROM hospital WHERE days IN(10,15);
Q4. Write a query to display pname and ward for all patients whose pname has 'm' as the third
alphabet.
SELECT pname, ward FROM hospital WHERE pname LIKE('__m%');
Q5. Write a query to display pname, ward and bill for all patients whose days are 10 and are not
getting discount.
SELECT pname, ward, bill FROM hospital WHERE days=10 AND discount IS NULL;
Practical 18: product-sale
CREATE TABLE product(prodid INT(4) PRIMARY KEY,

pname VARCHAR(10) NOT NULL, stock INT(3) NOT NULL);
INSERT INTO product VALUES(1001, 'PEN', 100);

INSERT INTO product VALUES(1002, 'PENCIL', 500);
INSERT INTO product VALUES(1003, 'COPY', 100);
INSERT INTO product VALUES(1004, 'BOOK', 200);
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE sale(

customerid INT(4) PRIMARY KEY,
cname VARCHAR(10) NOT NULL,
pid INT(4) NOT NULL,
quantity INT(3) NOT NULL,
price INT(4) NOT NULL,
CONSTRAINT FOREIGN KEY(pid) REFERENCES product(prodid)
) Engine='INNODB';
INSERT INTO sale VALUES(101, 'TOM', 1001, 5, 50);

INSERT INTO sale VALUES(102, 'BOB', 1001, 10, 50);
INSERT INTO sale VALUES(103, 'JIM', 1002, 15, 20);
INSERT INTO sale VALUES(104, 'SID', 1003, 20, 80);
INSERT INTO sale VALUES(105, 'KAY', 1002, 10, 50);
Q. WAQ to display the cname, product id , pname, and stock of all the sales whose quantity is > 10
SELECT cname, pid, pname, stock FROM sale, product WHERE sale.pid=product.prodid AND
quantity > 10;
Q. WAQ to display the cname, pname, quantity and stock for all the sales whose stock is less than
500.
SELECT cname, pname, quantity, stock FROM sale, product WHERE sale.pid=product.prodid AND
stock<500;
Q. Write a query to display pid and total price for each pid in the sales table.
SELECT pid, SUM(price) FROM sale GROUP BY pid;
Q. Write a query to display cname, quantity and price of all the sales whose price is greater than 20
and arrange the display in ascending order of price.
SELECT cname, quantity, price FROM sale WHERE price > 20 ORDER BY price DESC;
Q. Write a query to display pname and pname-wise minimum quantity in the product-sale tables.
SELECT pname, MIN(price) FROM sale, product WHERE sale.pid=product.pid GROUP BY pname;
Q. Write a query to display cname, quantity and price for all sales whose cname has an ‘O’ as second
alphabet.
SELECT cname, quantity, price FROM sales WHERE cname LIKE('_O%');
Practical 19: sportsclub
CREATE DATABASE yourname;

USE yourname
CREATE TABLE SPORTSCLUB(

playerid INT(4) PRIMARY KEY,
pname VARCHAR(10) NOT NULL,
dob DATE NOT NULL,
game VARCHAR(15) NOT NULL,
rating CHAR(1) NULL,
salary INT(5) NOT NULL);
INSERT INTO SPORTSCLUB VALUES(1001, 'VIRAT', '1988-01-10', 'CRICKET', 'A', 10000);

INSERT INTO SPORTSCLUB VALUES(1002, 'SUNIL', '1986-05-21', 'SOCCER', 'A', 8000);
INSERT INTO SPORTSCLUB VALUES(1003, 'SANIA', '1984-08-15', 'TENNIS', NULL, 2000);
INSERT INTO SPORTSCLUB VALUES(1004, 'SURYA', '1990-01-15', 'CRICKET', 'B', 6000);
INSERT INTO SPORTSCLUB VALUES(1005, 'RONALDO', '1988-05-18', 'SOCCER', 'A', 8000);
INSERT INTO SPORTSCLUB VALUES(1006, 'SAINA', '1987-08-10', 'BADMINTON', NULL, 3000);
Q.1 Query to display pname, game, rating for all players who has no rating.
SELECT pname, game, rating FROM sportsclub WHERE rating IS NULL;
Q.2 Query to display pname, game, salary for all players whse salary is in the range 8000 to 10000.
SELECT pname, game, salary FROM sportsclub WHERE salary BETWEEN 8000 AND 10000;
Q.3 Query to display game, number of players in each game in the table.
SELECT game, COUNT(*) FROM sportsclub GROUP BY game;
Q.4. Write a query to display pname, game and salary for all player whose game is either cricket or
soccer.
SELECT pname, game, salary FROM sportsclub WHERE game IN('CRICKET', 'SOCCER');
Q.5 Write a query to display the pname, game and salary for all players and arrange the display in
descending order of salary.
SELECT pname, game, salary FROM sportsclub ORDER BY salary DESC;
Practical 20: Student-hobby
CREATE TABLE housename(hid INT(2) PRIMARY KEY,

house VARCHAR(10) NOT NULL);
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE student(

rollno INT(2) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
age INT(2) NOT NULL,
class CHAR(4) NOT NULL,
gender CHAR(1) NOT NULL,
hid INT(2) NOT NULL,
CONSTRAINT FOREIGN KEY(hid) REFERENCES housename(hid)
)Engine="INNODB";
INSERT INTO housename VALUES(10, "SHIVAJI");

INSERT INTO housename VALUES(15, "TAGORE");
INSERT INTO housename VALUES(20, "ASHOKA");
INSERT INTO housename VALUES(30, "RAMAN");
INSERT INTO student VALUES(1,"AMAR", 16, "X", "M",10);

INSERT INTO student VALUES(2,"RAJA", 15, "IX", "M",15);
INSERT INTO student VALUES(3,"ROHAN", 16, "X", "M",20);
INSERT INTO student VALUES(4,"SUMA", 15, "IX", "F",10);
INSERT INTO student VALUES(5,"RADHA", 17, "XI", "F",15);
INSERT INTO student VALUES(6,"GEETHA", 17, "XI", "F",30);
Q1. Write a query to display the student details who do not have any hobby.
SELECT * FROM student WHERE hobby IS NULL;
Q2. Write a query to display the name, age and gender for all students whose hid is either 10 or 20.
SELECT name, age, gender FROM student WHERE hid IN(10,20);
Q3. Write a query to display the name, age and class of all the students whose name starts with the
alphabet R.
SELECT name, age, class FROM student WHERE name LIKE("R%");
Q4. Write a query to display the class and number of students in each class excluding class IX.
SELECT class, COUNT(*) FROM student GROUP by class having class <>"IX";
Q5. Write a query to display the name, age and house for all students whose age is greater than 16
years.
SELECT name, age, house FROM student, housename WHERE student.hid=housename.hid AND
age>16;

SQL Practicals Cs

Uploaded by

Copyright:

Available Formats

SQL Practicals Cs

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Practicals Cs

Uploaded by

Copyright:

Available Formats

Practical 16 – EMP-DEPT

USE DATABASE yourdbname

CREATE TABLE emp(

INSERT INTO dept VALUES(10,'ADM','DELHI');

INSERT INTO emp VALUES(1001, 'AMAY', 10, 'M', 8000, 500);

SELECT gender, COUNT(*) FROM emp GROUP BY gender;

Q5. Write a query to display dname, total salary in each dname.

SELECT dname, SUM(salary) FROM emp, dept WHERE emp.deptno=dept.deptno GROUP BY

CREATE TABLE hospital(

INSERT INTO hospital VALUES(1001, 'Rohit', 'Medicine', 15,10000, NULL);

CREATE TABLE product(prodid INT(4) PRIMARY KEY,

INSERT INTO product VALUES(1001, 'PEN', 100);

CREATE TABLE sale(

INSERT INTO sale VALUES(101, 'TOM', 1001, 5, 50);

CREATE DATABASE yourname;

CREATE TABLE SPORTSCLUB(

INSERT INTO SPORTSCLUB VALUES(1001, 'VIRAT', '1988-01-10', 'CRICKET', 'A', 10000);

CREATE TABLE housename(hid INT(2) PRIMARY KEY,

CREATE TABLE student(

INSERT INTO housename VALUES(10, "SHIVAJI");

INSERT INTO student VALUES(1,"AMAR", 16, "X", "M",10);

You might also like