Maharshi Patanjali Vidya Mandir
Class 10 – Information Technology
Session 6 : Queries using SQL
Lab Assignment
1. Creating Database:
create database stu_info1;
2. Selecting the database:
use stu_info1;
3. Creating Student table inside stu_info1 database:
create table student (adm_no int,name varchar(30),dob date, marks int);
4. Viewing tables inside the database:
show tables;
5. Viewing structure of created table:
describe student;
6. Inserting records into the Student Table: (using short form of query)
insert into student values(101,"Suresh",'2007-09-06',67);
insert into student values(102,"Pankaj",'2008-10-06',75);
insert into student values(103,"Manish",'2009-11-03',70);
insert into student values(104,"Manoj",'2006-10-06',80);
7. Displaying all the fields and records of the Student table:
select * from student;
8. Using long form of insert query (by specifying field names in bracket
before values) when we need to skip inserting the value of marks field
in the record:
insert into student (adm_no,name,dob) values(105,"Neha",'2008-03-
07');
9. Inserting a record with duplicate value of Name Field:
insert into student values(106,"Manoj",'2005-04-06',68);
10.Again, check the content of the table:
select * from student;
11.Selecting particular columns to display from the table:
select adm_no,name from student;
12.Selecting records from table by specifying condition using where clause
in SELECT query:
select * from student where adm_no=101;
13.Selecting those students records who were born on or after 1st March
2008:
select * from student where dob>='2008-01-01';
14.Selecting those students records who were born on or before 1st
March 2008:
select * from student where dob<='2008-01-01';
15.Applying multiple conditions through OR (any one of the conditions
needs to be satisfied):
select * from student where adm_no=102 or name="Suresh";
16.Applying multiple conditions through AND (both the conditions needs
to be satisfied):
select * from student where adm_no=101 and name="Suresh";
17.Eliminating Redundant data with DISTINCT keyword (in name field):
select distinct name from student;
18.Selecting from all the rows using ALL Keyword :
select all name from student;
19.(a)Performing Simple Calculations:
select 3.14159*6*6;
20.Using Column Aliases (using AS Keyword):
select adm_no,name as stu_name from student;
21.To obtain current system date
select curdate( );
22.Condition based on a Range (using BETWEEN keyword):
select adm_no,name from student where marks between 80 and 90;
ALTERNATIVELY (use AND keyword to combine two conditions)
select adm_no,name from student where marks>=80 and marks<=90;
23.Condition based on a LIST (using IN keyword):
select * from student where name in
('Suresh','Pankaj','Manish','Sneha','Vimal');
USING NOT IN (for selecting records that are not matching)
select * from student where name not in
('Suresh','Pankaj','Manish','Sneha','Vimal');
24.Condition based on Pattern Matches ( using AS keyword and wildcard
characters % and _):
(a) Fetch records with Name starting with M.
select * from student where name LIKE 'M%';
(b) Fetch records with Name ending with h.
select * from student where name like '%h';
(c) Fetch records with name starting with S and having ‘resh’ at the 3rd ,
4th , 5th and 6th places respectively: (using _ character as wild card)
select * from student where name like 's_resh';
25. Searching for NULL values : (using IS NULL and IS NOT NULL)
select * from student where marks is NULL;
select * from student where marks is not NULL;
26.Modifying Data using UPDATE command:
(a) Update data as per specified condition in where clause :
update student set name="Satish" where adm_no=102;
Display all records of the table:
select * from student;
(b) Update data without any condition:
update student set marks=40;
Display all records of the table:
select * from student;
27.Deleting data from tables:
(a) Deleting as per condition specified in where clause:
delete from student where name="Neha";
Checking records present in Table:
select * from student;
(b) Deleting without specifying any condition:
delete from student;
Checking records present in Table:
select * from student;
