1)Create a table Student with the following fields and insert at least 5 records into the table except

for the column Total.

Roll Number Integer Primary key,Name Varchar (25), Batch Varchar (15), Mark1 Integer, Mark2
Integer, Mark3 Integer,Total Integer

1. Update the column Total with the sum of Mark1, Mark2 and Mark3.

2. List the details of students in Commerce batch.

3. Display the name and total marks of students who are failed (Total less

than 90).

4. Display the name of students in alphabetical order and in batch based

CREATE TABLE student (roll_number INT NOT NULL PRIMARY KEY ,stud_name VARCHAR (25) , batch
VARCHAR (25) ,mark1 INT , mark2 INT , mark3 INT , total INT);

insert into student values

(1, ’Alan Turing ’, ’Science ’ ,80, 70, 55, null );

insert into student values

(2, ’Ada Lovelace ’, ’Science ’ ,30, 40, 15, null );

insert into student values

(3, ’Adam Smith ’, ’Commerce ’, 70, 60, 40, null );

insert into student values

(4, ’Amartya Sen ’, ’Commerce ’ ,40, 80, 50, null );

insert into student values

(5, ’Ibn Rushd’, ’Science ’, 90, 94, 35, null );

insert into student values

(6, ’Irfan Habeeb ’, ’Humanities ’, 80, 76, 69, null );

insert into student values

(7, ’Friedman ’, ’Commerce ’, 60, 60, 50, null );

insert into student values

(8, ’Robinson ’, ’Commerce ’, 50, 70, 50, null );

SELECT * FROM student;

1. UPDATE student SET total = mark1 + mark2 + mark3 ;

2. SELECT * FROM student WHERE batch = ’Commerce’;

3. select stud_name , total from student where total <90;

4. select stud_name , batch from student order by batch,stud_name ;

OUTPUT

Complete student details of Commerce batch

Result of select query total <90

Result of select - order by query

2)Create a table employee with the following fields and insert at least 5 records into the table except
the column Gross pay and DA.

Emp code Integer Primary key, Emp name Varchar (20),Designation Varchar (25), Department Varchar
(25), Basic Decimal (10,2) DA Decimal (10,2), Gross pay Decimal (10,2)

1. Update DA with 75% of Basic.

2. Display the details of employees in Purchase, Sales and HR departments.

3. Update the Gross pay with the sum of Basic and DA.

4. Display the details of employee with gross pay below 20000

CREATE TABLE employee (Emp_code Int PRIMARY KEY,Emp_name VARCHAR (20),Designation

VARCHAR (25),Department VARCHAR (25) ,Basic DEC (10 ,2) ,DA DEC (10 ,2),Gross_pay DEC (10 ,2));

INSERT INTO employee values

(100 , ’Arjun ’,’Trainees ’,’Sales ’ ,16000.00 , NULL , NULL ),

INSERT INTO employee values

(101 , ’Maya ’,’Trainees ’,’Purchase ’ ,9000.00 , NULL , NULL ),

INSERT INTO employee values

(102 , ’Rahul ’,’Trainees ’,’Purchase ’ ,10000.00 , NULL , NULL ),

INSERT INTO employee values

(103 , ’Ibrahim ’,’Supervisors ’,’Purchase ’ ,18000.00 , NULL,NULL ),

INSERT INTO employee values

(104 , ’Abdulla ’,’Supervisors ’,’Sales ’ ,21000.00 , NULL , NULL),

INSERT INTO employee values

(105 , ’Vivek ’,’Managers ’,’HR ’ ,32000.00 , NULL , NULL ),

INSERT INTO employee values

(106 , ’George ’,’Managers ’,’Sales ’ ,45000.00 , NULL , NULL ),

INSERT INTO employee values

(107 , ’Meera ’,’Trainees ’,’HR ’ ,11000.00 , NULL , NULL ),

INSERT INTO employee values

(108 , ’Tiji’,’Trainees ’,’IT ’ ,12000.00 , NULL , NULL );

1. UPDATE employee SET DA= Basic *75/100;

[Link] * FROM employee WHERE Department IN (’Purchase ’, ’Sales ’,’HR ’);

3. UPDATE employee SET Gross_pay = Basic+DA;

4. SELECT * FROM employee WHERE Gross_pay < 20000;

OUTPUT
Result of select query from employee table

Resultant table of select query from the employee table with Gross pay <20000

3)Create a table stock which stores daily sales of items in a shop with the following fields and insert
atleast 5 records into the table

Item code Integer Primary key, Item name Varchar (20),Manufacturer Code Varchar (5), Qty Integer,
Unit Price Decimal (10,2)

1. Display the item name with stock zero.

2. Display the number of items manufactured by the same manufacturer.

3. Display the highest price and lowest quantity in the stock

4. Increase the unit price of all items by 10%

CREATE TABLE stock ( Item_code INTEGER PRIMARY KEY , Item_name VARCHAR (20) ,
Manufacturer_Code VARCHAR (5) , Qty INT , Unit_Price DECIMAL (10 ,2));

INSERT INTO stock values

(’1001 ’, ’Smart Phone’, ’Samsung ’, ’23 ’, ’36000’);

INSERT INTO stock values

(’1002 ’, ’Smart Watch’, ’Samsung’, ’16’, ’19000’);

INSERT INTO stock values

(’1003 ’, ’Washing Machine’, ’LG’, ’21’, ’36000 ’);

INSERT INTO stock values

(’1004 ’, ’Juicer’, ’Butterfly ’, ’5’, ’7000’);

INSERT INTO stock values

(’1005 ’, ’Toaster’, ’Philips ’,’0’, ’3000 ’);

INSERT INTO stock values

(’1006 ’, ’Air Purifier, ’Philips’, ’3’, ’9500’);

INSERT INTO stock values

(’1007 ’, ’Coffee Maker’, ’Havels’, ’0’, ’16000’);

1. SELECT * FROM stock WHERE Qty =0;

2. SELECT Manufacturer_Code , COUNT ( Item_code ) AS No_Products FROM stock GROUP BY

Manufacturer_Code;

3. SELECT MAX ( Unit_price ) AS Highest_price , MIN (Qty ) AS Min_Qty FROM stock;

4. UPDATE stock SET Unit_Price = Unit_Price + Unit_Price *.1;

OUTPUT
Resultant table of select query from the stock table with stock = 0

The quantity of items produced by same manufacturer from the stock Database

The table resulting from the SELECT query

After executing the Update Query

4)Create a table bank with the following fields and insert at least 5 records into the table.
Acc Number Integer Primary key, Acc Name Varchar (20),Branch Name Varchar (25), Acc Type Varchar
(10) , Amount Decimal (10,2)

1. Display the account details account in Kodungallur Branch

2. Change the branch name Trivandrum to Thiruvananthapuram.

3. Display the details of customers in Thiruvananthapuram, Eranakulam and Kozhikkode

4. List the details of customers in Trissur branch having a minimum balance of Rs. 5000

CREATE TABLE bank (Acc_No INT NOT NULL PRIMARY KEY , Acc_Name ‘ VARCHAR (20) ,
Branch_Name VARCHAR (25) , Acc_Type VARCHAR (10), Amount DECIMAL (10 ,2));

INSERT INTO bank values

(’150001 ’, ’Peter ’, ’Alappuzha ’, ’Current ’, ’65000 ’);

INSERT INTO bank values

(’150002 ’, ’Hamid ’, ’Trivandrum ’, ’Savings ’, ’12000 ’);

INSERT INTO bank values

(’150003 ’, ’Anoop ’, ’Malappuram ’, ’Savings ’, ’4000 ’);

INSERT INTO bank values

(’150004 ’, ’Jyothi ’, ’Trissur ’, ’Current ’, ’75000 ’);

INSERT INTO bank values

(’150005 ’, ’Azim ’, ’Ernakulam ’, ’Current ’, ’58600 ’);

INSERT INTO bank values

(’150006 ’, ’Ayra ’, ’Kozhikode ’, ’Savings ’, ’7500 ’);

INSERT INTO bank values

(’150007 ’, ’Amosh ’, ’Thrissur ’, ’Savings ’, ’3500 ’);

INSERT INTO bank values

(’150008 ’, ’Sidharth ’, ’ Kodungallore ’, ’Savings ’, ’69200 ’);

[Link] * FROM bank WHERE Branch_Name =" Kodungallore";

[Link] bank SET Branch_Name =" Thiruvananthapuram " WHERE Branch_Name =" Trivandrum ";
[Link] * FROM bank WHERE Branch_Name IN(’Thiruvananthapuram ’, ’Kozhikkod ’, ’Ernakulam’);

[Link] * FROM bank WHERE Branch_Name =’Trissur ’HAVING Amount < 5000;

OUTPUT

5)Create a table book with the following fields and insert at least 5 records into the table.

Book Id Integer Primary key, Book Name Varchar (20), Author Name Varchar (25), Pub Name Varchar
(25), Price Decimal (10,2)

1. Display the details of book with price 100 or more.

2. Display the names of all books published by SCERT.

3. Increase the price of books by 10% which are published by SCERT.

4. List the details of books with the title containing the word “Programming” at the end.

CREATE TABLE book (Book_ID INT NOT NULL PRIMARY KEY,Book_Name VARCHAR (20),Author_Name
VARCHAR (25),Pub_Name VARCHAR (25),Price DECIMAL (10 ,2));

INSERT INTO book values

(’1200 ’, ’The Kite Runner ’, ’Khaled Husseini ’, ’River Head ’, ’450 ’);

INSERT INTO book values

(’1201 ’, ’Wings of Fire ’, ’Dr. APJ Abdulkalam ’, ’Universities Press ’, ’250 ’);

INSERT INTO book values

(’1202 ’, ’The White Tiger ’, ’Aravind Adiga ’, ’Atlantic ’, ’300 ’);

INSERT INTO book values

(’1203 ’, ’Manju ’, ’M T Vasudevan Nair’, ’DC Books ’, ’425 ’);

INSERT INTO book values

(’1204 ’, ’Arachar ’, ’K R Meera ’, ’DC Books ’, ’225 ’);

INSERT INTO book values

(’1205 ’, ’When Breath Becomes Air ’, ’paul Kalanidhi ’, ’Random House ’, ’525 ’);

INSERT INTO book values

(’1206 ’, ’Forty Rules of Love ’, ’ElifShefak ’, ’Penguine ’, ’700 ’);

INSERT INTO book values

(’1207 ’, ’Art of Computer Programming’, ’Peter ’, ’SCERT ’, ’125 ’);

INSERT INTO book values

(’1208 ’, ’Madhuram Malayalam ’, ’Madhu Vaduthala ’, ’SCERT ’, ’75 ’);

INSERT INTO book values

(’1209 ’, ’Python Programming ’, ’Michael Dawson ’, ’Pearson ’, ’3300 ’);

1. SELECT * FROM book WHERE Price >= 100;

2. SELECT * FROM book WHERE Pub_Name = ’SCERT’;

3. UPDATE book SET Price = Price + Price *0.1 WHERE Pub_Name = ’SCERT ’;

4: SELECT * FROM BOOK WHERE Book_Name LIKE ’%Programming ’;

OUTPUT