GRADE 12 : PRACTICAL 16

 Create the below tables and perform the required queries:

FURNITURE:

+----+-----------------+--------------+-------------+----------+----------+

| no | itemname        | type         | dateofstock | price    | discount |

+----+-----------------+--------------+-------------+----------+----------+

|  1 | dolphin         | baby cot     | 19-02-2002  |  9500.00 |       20 |

|  2 | decent          | office table | 01-01-2002  | 25000.00 |       30 |

|  3 | comfort zone    | double bed   | 12-01-2002  | 25000.00 |       25 |

|  4 | donald          | baby cot     | 24-02-2002  |  6500.00 |       15 |

|  5 | royal finish    | office table | 20-02-2002  | 18000.00 |       90 |

|  6 | royal tiger     | sofa         | 22-02-2002  | 31000.00 |       30 |

|  7 | economy sitting | sofa         | 13-12-2001  |  9500.00 |       25 |

|  8 | eating paradise | dining table | 19-02-2002  | 11500.00 |       25 |

+----+-----------------+--------------+-------------+----------+----------+  

ARRIVALS:

+----+--------------+------------+-------------+----------+----------+

| no | itemname     | type       | dateofstock | price    | discount |

+----+--------------+------------+-------------+----------+----------+

|  1 | wood comfort | double bed | 23-03-2003  | 25000.00 |       25 |

|  2 | old fox      | sofa       | 20-03-2003  | 17000.00 |       20 |

|  3 | micky        | baby cot   | 1-4-2003    |  7500.00 |       15 |

+----+--------------+------------+-------------+----------+----------+

CREATE STATEMENTS :

CREATE DATABASE lab16;

USE lab16;

create table furniture(

no int primary key,

itemname varchar(50) not null, 

type varchar(50) not null,

dateofstock date not null,

price decimal(8,2), 

discount numeric(2)

);

create table arrivals(

no int primary key , 

itemname varchar(50) not null, 

type varchar(50) not null,

dateofstock date not null,

price decimal(8,2) not null, 

discount numeric(2) 

);

insert into furniture values 

(1,'dolphin','baby cot','2002-02-19',9500.00,20),

(2,'decent','office table','2002-01-01',25000.00,30),

(3,'comfort zone','double bed','2002-01-12',25000.00,25),

(4,'donald','baby cot','2002-02-24',6500.00,15),

(5,'royal finish','office table','2002-02-20',18000.00,90),

(6,'royal tiger','sofa','2002-02-22',31000.00,30),

(7,'economy sitting','sofa','2001-12-13',9500.00,25),

(8,'eating paradise','dining table','2002-02-19',11500.00,25);

insert into arrivals values 

(1,'wood comfort','double bed','2003-03-23',25000.00,25),

(2,'old fox','sofa','2003-03-20',17000.00,20),

(3,'micky','baby cot','2003-4-1',7500.00,15);

QUERIES

i)List itemnames and types of those items which were stocked in February from the furniture table in descending order of the item name

ii)Display item name and date of stock of those items in which the discount percentage is less than 20 or more than 25.

iii)Display the number of furniture items for each type

iv) Find the maximum discount offered on each furniture type

v)Display furniture items having a price greater than the average price of all arrivals.

vi)Display furniture types that are not present in the arrivals table

vii) Display item no, item name, mrp and the discounted price for all items

viii)Move all the rows from arrivals into the furniture table

ix)Increase the discount by 5 for all 'sofa' type items in the furniture table.

x)Remove all furniture items that were stocked before January 1, 2002.

GRADE 12 : PRACTICAL 15

  

Create the below tables : 

Student:

Stream:

CREATE TABLE STREAM (
    s_id INT PRIMARY KEY,
    s_name VARCHAR(20)
);
CREATE TABLE STUDENT (
    adno INT PRIMARY KEY,
    fname VARCHAR(20),
    lname VARCHAR(20),
    class VARCHAR(5),
    sec CHAR(1),
    fee INT,
    mobile BIGINT,
    area VARCHAR(30),
    s_id INT,
    FOREIGN KEY (s_id) REFERENCES STREAM(s_id)
);

INSERT INTO STREAM VALUES 

(10, 'PCMC'),

(20, 'PCMB'),

(30, 'PCBH'),

(40, 'HECA'),

(50, 'MECA'),

(60, 'Arts');

INSERT INTO STUDENT VALUES 

(1001, 'Ramesh', 'Babu', 'xii', 'a', 5000, 9988776655, 'Benson Town', 10),

(1002, 'Dipti', 'Rai', 'xii', 'b', 4500, 8877665599, 'Cooke Town', 20),

(1003, 'Rahul', 'Pai', 'xii', 'c', 4000, 7766559988, 'Fraser Town', 30),

(1004, 'Shruthi', 'Saravanan', 'xii', 'c', 4000, 6655998877, 'Benson Town', 30),

(1005, 'George', 'Davis', 'xi', 'a', 4500, 5599887766, 'Cooke town', 10),

(1006, 'Tinu', 'Anna', 'xi', 'b', 4200, 9988776611, 'Benson Town', 20),

(1007, 'Abrar', 'Sultan', 'xi', 'c', 4100, 9988776622, 'Cooke town', 40),

(1008, 'Iffath', 'Khan', 'xii', 'a', 5000, 9988776633, 'Fraser Town', 10),

(1009, 'Hansika', 'Singh', 'xii', 'b', 4500, 9988776644, 'Cleveland Town', 20),

(1010, 'Kailash', 'Dharyanani', 'xii', 'c', 4000, 9988776633, 'Richard Town', 50);

1)Display each student's full name along with their stream name.

SELECT CONCAT(fname,' ',lname) AS FullName, s_name

FROM STUDENT NATURAL JOIN STREAM;

2)List the names of streams and the number of students in each.

SELECT s_name, COUNT(*) AS Num_Students

FROM STREAM NATURAL JOIN STUDENT 

GROUP BY s_name;

3)Find the total fee collected from each class

SELECT class, SUM(fee) AS Total_Fee

FROM STUDENT

GROUP BY class;

4)List the average fee paid by students in each stream

SELECT s_name, AVG(fee) AS Avg_Fee

FROM STUDENT NATURAL JOIN STREAM 

GROUP BY s_name;

5)Display the student(s) who paid the highest fee.

SELECT fname, lname, fee

FROM STUDENT

WHERE fee = (SELECT MAX(fee) FROM STUDENT);

6)Display the stream name and the total fee collected (highest total first), where the fee collected is more than Rs 4000 

SELECT s_name,SUM(fee)

FROM Student NATURAL JOIN Stream

GROUP BY s_name

HAVING SUM(fee) > 4000

ORDER BY SUM(fee) desc;

7)Increase the fee by 500 for all students in the ‘PCMC’ stream.

UPDATE STUDENT

SET fee = fee + 500

WHERE s_id = (SELECT s_id FROM STREAM WHERE s_name = 'PCMC');

8)Update mobile number of student 'George Davis' to 9123456789.

UPDATE STUDENT

SET mobile = 9123456789

WHERE fname = 'George' AND lname = 'Davis';

9)Delete all students in the 'Arts' stream.

DELETE FROM STUDENT

WHERE s_id = (SELECT s_id FROM STREAM WHERE s_name = 'Arts');

10)Delete all students whose area starts with C and the first word has atleast 6 letters and ends with town.

DELETE FROM STUDENT

WHERE area LIKE 'C_____% Town';

GRADE 12 : PRACTICAL 14

 Create the below tables : 

DEPARTMENT : 

TEACHER: 

(Include the following constraints : 

> Check that 20<=age<=70

> Sex should take only M and F values) 

SOLUTION 

CREATE TABLE department (
         DeptName VARCHAR(20) PRIMARY KEY,
         HOD VARCHAR(30),
         RoomNo VARCHAR(10),
         Budget NUMERIC(7)
);
INSERT INTO department VALUES
         ('Computer', 'Dr. Sen', 'C101', 500000),
         ('History', 'Dr. Malhotra', 'H202', 300000),
         ('Math', 'Dr. Iyer', 'M303', 400000);
CREATE TABLE teacher (
    No INT PRIMARY KEY ,
    Name VARCHAR(20) NOT NULL,
    Age NUMERIC(2) CHECK (Age BETWEEN 20 AND 70),
    Department VARCHAR(20),
    DOJ DATE,
    Salary FLOAT,
    Sex VARCHAR(1) CHECK (Sex IN ('M', 'F')),
    FOREIGN KEY (Department) REFERENCES department(DeptName)
);
INSERT INTO teacher  VALUES
    (111,'Jugal', 34, 'Computer', '1997-01-10', 12000, 'M'),
    (222,'Sharmila', 31, 'History', '1998-03-24', 20000, 'F'),
    (333,'Sandeep', 32, 'Math', '1996-12-12', 30000, 'M'),
    (444,'Sangeeta', 35, 'History', '1999-07-01', 40000, 'F'),
    (555,'Rakesh', 42, 'Math', '1997-09-05', 25000, 'M'),
    (666,'Shyam', 50, NULL, '1998-06-27', 30000, 'M'),
    (777,'Shiv Om', 44, 'Computer', '1997-02-25', 21000, 'M'),
    (888,'Shalaka', 33, 'Math', '1997-07-31', 20000, 'F');

PRACTICE : 

1. Display the unique departments from Teacher Table.
SELECT DISTINCT Department 

FROM teacher 

WHERE Department IS NOT NULL;

2. Display the RECORD COUNT , DEPARTMENT COUNT(with repeats but without NULL)  in teacher table;
SELECT COUNT(*) AS RECORD_COUNT,COUNT(Department) AS DEPARTMENT_COUNT 

FROM teacher;

3.Display the number of teachers in each department.

SELECT Department, COUNT(*) AS Num_Teachers

FROM teacher

WHERE Department IS NOT NULL

GROUP BY Department;

4.Find the average salary of teachers in each department in increasing order of their average salaries.

SELECT Department, AVG(Salary) AS Avg_Salary

FROM teacher

WHERE Department IS NOT NULL

GROUP BY Department

ORDER BY AVG(Salary);

5.Show the names of departments having more than 2 teachers.

SELECT Department, COUNT(*) AS Teacher_Count

FROM teacher

WHERE Department IS NOT NULL

GROUP BY Department

HAVING COUNT(*) > 2;

6.Show department name, budget, and number of teachers in each department :

SELECT DeptName,Budget,COUNT(No) AS Teacher_Count

FROM department JOIN teacher ON DeptName = Department

GROUP BY DeptName,Budget;

or

SELECT DeptName,Budget,COUNT(No) AS Teacher_Count

FROM department , teacher WHERE DeptName = Department

GROUP BY DeptName,Budget;

7.Display the average salary per sex, in descending order of the average salary(Rounded to nearest hundreds)

SELECT Sex, round(avg(salary),-2) as Avg_Sal

FROM teacher

GROUP BY Sex

ORDER BY Avg_Sal desc;

8.Display the Name, Deparment and DOJ of the earliest joining teacher
SELECT Department, Name, DOJ
FROM teacher
WHERE DOJ = (
    SELECT MIN(DOJ)
    FROM teacher);

9.Increase the salary of all teachers in the 'Math' department by 10%

UPDATE teacher

SET Salary = Salary * 1.10

WHERE Department = 'Math';

10.Remove all teachers who are not assigned to any department.

DELETE FROM teacher

WHERE Department IS NULL;

Menagerie

CREATE DATABASE menagerie;

USE menagerie;

DROP TABLE IF EXISTS pet;

CREATE TABLE pet

(

  name    VARCHAR(20),

  owner   VARCHAR(20),

  species VARCHAR(20),

  sex     CHAR(1),

  birth   DATE,

  death   DATE

);

INSERT INTO pet VALUES('Fluffy','Harold','cat','f','1993-02-04',NULL);

INSERT INTO pet VALUES('Claws','Gwen','cat','m','1994-03-17',NULL);

INSERT INTO pet VALUES('Buffy','Harold','dog','f','1989-05-13',NULL);

INSERT INTO pet VALUES('Fang','Benny','dog','m','1990-08-27',NULL);

INSERT INTO pet VALUES('Bowser','Diane','dog','m','1979-08-31','1995-07-29');

INSERT INTO pet VALUES('Chirpy','Gwen','bird','f','1998-09-11',NULL);

INSERT INTO pet VALUES('Whistler','Gwen','bird',NULL,'1997-12-09',NULL);

INSERT INTO pet VALUES('Slim','Benny','snake','f','1996-04-29',NULL);

INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

DROP TABLE IF EXISTS event;

CREATE TABLE event

(

  name   VARCHAR(20),

  date   DATE,

  type   VARCHAR(15),

  remark VARCHAR(255) DEFAULT NULL

);

INSERT INTO EVENT VALUES("Fluffy","1995-05-15","litter","4 kittens, 3 female, 1 male");

INSERT INTO EVENT VALUES("Buffy","1993-06-23","litter","5 puppies, 2 female, 3 male");

INSERT INTO EVENT VALUES("Chirpy","1999-03-21","vet","needed beak straightened");

INSERT INTO EVENT VALUES("Slim","1997-08-03","vet","broken rib");

INSERT INTO EVENT(name,date,type) VALUES("Bowser","1991-10-12","kennel");

INSERT INTO EVENT(name,date,type)  VALUES("Fang","1991-10-12","kennel");

INSERT INTO EVENT VALUES("Fang","1998-08-28","birthday","Gave him a new chew toy");

INSERT INTO EVENT  VALUES("Claws","1998-03-17","birthday","Gave him a new flea collar");

INSERT INTO EVENT VALUES("Whistler","1998-12-09","birthday","First birthday");