Lab 1

Exercise 1  (practice on SQL commands)

a )  DDL commands

 Creating a table

create table friends ( id int primary key, first varchar(50) not null , last varchar(50) not null);

Altering a  table 

alter table friends add constraint chk_id check(id<10);

b) DML commands

 Drop a table

drop table friends;

Truncating a  table

truncate table friends;

Rename

rename table friends to dost;

Inserting Rows into table

insert into friends values(3,"Aman","Nigam")

Deleting Rows from table

delete from friends where first="ROHIT";

Updating data in a table

update friends set last="momos wala" where first="ayush"

 Defining constraints

alter table friends add constraint chk_id check(id<10);
-----------------------------------------------------------------------------------------------------------------------------

Exercise 2( Assignment on DDL and DML commands )

A Suppose that a faculty wants to keep records about the students, the desired records should maintain this information about each student:

   (Roll No, Full name, CGPA, Age, and Gender)

a) Create the appropriate Table.

create table student ( roll int primary key, name varchar(50) not null, cgpa float not null, age int not null, gender varchar(10) not null );

insert into student values(20123047,"Dhruv",8.52,20,"male");
insert into student values(20124072,"Arpit Rathi",9.2,21,"male");
insert into student values(20128058,"Aashish Tyagrajan",6.8,20,"male");
insert into student values(20125050,"Aman Mittal",8.17,21,"male");
insert into student values(20120053,"Supriya Singh",8.75,19,"female");

 b) Write SQL Queries to answer the following problems:

1. List the names of all female students.
   SELECT * from student where gender="female"; 
2. What is the age of the youngest male student?
   Select name,age from student where gender like "male" order by age asc limit 1;
    
3. List the name and the CGPA for all students who got above 6.5 CGPA ordered by their CGPAs then their names.
   Select cgpa,name from student where CGPA>6.5 order by CGPA desc;
    
4. What is the Topper name?
   Select cgpa,name from student where CGPA>6.5 order by CGPA desc limit 1;

c) Since keeping the age of the student as an attribute requires frequent changes (each year) propose a
solution and implement it.

update student set age=age+1;

----------------------------------------------------------------------------------------------------------------------------

Exercise 3(Assignment on DDL and DML commands)

A supermarket manager likes to keep records about all the items in his store these records should hold the following information about each item :
(the item_id, the item name, the item price, expiration date of the item, quantity in hand)

a) Create the appropriate table.

create table item( id int primary key, name varchar(50) not null, price float not null, exp date, quantity int not null );

insert into item values(1,"sauce",15.50,10/11/2016,10);//date wrong
insert into item values(2,"burger",25.50,10/11/2016,5);//date wrong
insert into item values(3,"chicken",80,10/11/2016,1);//date wrong
insert into item values(4,"mutton",80,10/11/2016,1);//date wrong
insert into item values(6,"mutton",80,'2015-11-10',1);

b) Write SQL queries to answer the following problems:

1.  List the names and prices for all items that have a quantity in hand >20.
   select name,price from item where quantity>5;
    
2. List the names of expired items.
   select * from item where exp<curdate();
    
3. Name the second most expensive item.
   select * from item order by price desc limit 1,1;
    
4. Alter the table structure to make sure that no negative value can be assigned to the price field
   alter table item add check (price>=0);