Scribd
Upload
37 views6 pages

SQL File

This document provides instructions for various SQL commands used to create and manage databases and tables, insert data, and write queries. It includes commands to create and drop databases and tables, add/alter/drop columns and constraints, insert data, and delete rows. It also lists common query keywords, operators, and example queries using selection criteria, ordering, aggregation, and modification statements.

Uploaded by

Hina Naveed
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
37 views6 pages

SQL File

This document provides instructions for various SQL commands used to create and manage databases and tables, insert data, and write queries. It includes commands to create and drop databases and tables, add/alter/drop columns and constraints, insert data, and delete rows. It also lists common query keywords, operators, and example queries using selection criteria, ordering, aggregation, and modification statements.

Uploaded by

Hina Naveed
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 6

Lab 6 (SQL Session 1)

A. Create DB
B. Drop DB
C. Create Table
D. Drop Table
E. Alter Table (Add columns)
F. Constraints
o Primary key
o Foreign key
o Default
o Not null
G. Insert Into (Data entry)
H. Delete

Data Types Description


CHAR(size) Holds a fixed length string (can contain letters, numbers, and special
characters). The fixed size is specified in parenthesis. Can store up to 255
characters
VARCHAR(size) Holds a variable length string (can contain letters, numbers, and special
characters). The maximum size is specified in parenthesis. Can store up to
255 characters.
INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*.
The maximum number of digits may be specified in parenthesis
DATE
A date. Format: YYYY-MM-DD

Note: The supported range is from '1000-01-01' to '9999-12-31'


TIME A time. Format: HH:MI:SS

Note: The supported range is from '-838:59:59' to '838:59:59'


1. Create database MISLAB1
2. Drop Database MISLAB1

3. Use MISLAB1
4. Create table Students (StudentID int, FirstName varchar(100), LastName varchar(100),
CGPA varchar(100), PhoneNo varchar(10), City varchar(100),Country varchar(50))

5. Drop Table Students

6. Select * from Students

7A. Alter table Students Add Specialization varchar

7B. Alter table students Alter column CGPA int

8. Alter table students drop column specialization

9. Create table Students (StudentID int Not Null , FirstName varchar(100) Not Null,
LastName varchar(100) Not Null, CGPA varchar(100) Not Null,
PhoneNo varchar(10) Not Null, City varchar(100) Not Null,Country varchar(50) Not
Null)

10. Alter table students Add primary key(studentID)

11. Create table SubjectMarks (StudentID int, Marketing varchar (100), Sociology varchar
(100), Finance varchar (100))

Drop table subjectmarks


select * from subjectmarks

12. Alter table subjectmarks Add foreign key (StudentID) references Students(StudentID)

13. Alter table students Add constraint DF_students_CGPA Default 0 for CGPA.

14. Alter table students drop constraint DF_students_CGPA

15. Create table Students (StudentID int Not Null , FirstName varchar(100) Not Null,
LastName varchar(100) Not Null, CGPA varchar(100) Not Null default'0',
PhoneNo varchar (10) Not Null, City varchar(100) Not Null,Country varchar(50) Not
Null)

16. Insert into Students (StudentID, FirstName, LastName, CGPA, PhoneNo, City, Country)
values ( '1', 'Aroosa', 'Safdar', '3.9', '10000001', 'Lahore', 'Pakistan')
17. Delete from students where studentID='1'
18. select * from Students

DATA ENTRY
Insert into Students (StudentID, FirstName, LastName, CGPA, PhoneNo, City, Country) values (
'2', 'Tahreem', 'Aslam', '3.45', '10000002', 'Faisalabad', 'Pakistan')
Insert into Students (StudentID, FirstName, LastName, CGPA, PhoneNo, City, Country) values (
'3', 'Fatima', 'Mustafa', '3.1', '10000003', 'Lahore', 'Pakistan')
Insert into Students (StudentID, FirstName, LastName, CGPA, PhoneNo, City, Country) values (
'4', 'Danyal', 'Sajid', '2.78', '10000004', 'Islamabad', 'Pakistan')
Insert into Students (StudentID, FirstName, LastName, CGPA, PhoneNo, City, Country) values (
'5', 'Hira', 'Khalid', '3.78', '10000005', 'Multan', 'Pakistan')
Insert into Students (StudentID, FirstName, LastName, CGPA, PhoneNo, City, Country) values (
'6', 'Hanana', 'Irfan', '3.58', '10000006', 'Lahore', 'Pakistan')
Insert into Students (StudentID, FirstName, LastName, CGPA, PhoneNo, City, Country) values (
'7', 'Ali', 'Imtiaz', '2.99', '10000007', 'Multan', 'Pakistan')
Insert into Students (StudentID, FirstName, LastName, CGPA, PhoneNo, City, Country) values (
'8', 'Ayesha', 'Ali', '3.31', '10000008', 'Lahore', 'Pakistan')
Insert into Students (StudentID, FirstName, LastName, CGPA, PhoneNo, City, Country) values (
'9', 'Umair', 'Yousaf', '3.33', '10000009', 'Lahore', 'Pakistan')
Insert into Students (StudentID, FirstName, LastName, CGPA, PhoneNo, City, Country) values (
'10', 'Khurram', 'Niaz', '2.47', '10000010', 'Islamabad', 'Pakistan')
Insert into Students (StudentID, FirstName, LastName, PhoneNo, City, Country) values ( '11',
'Kashif', 'Niaz', '10000011', 'Islamabad', 'Pakistan')

Lab 7 (SQL Session 2)


Query Statements/Keywords/Operators
A. Select
B. Where
C. OR
D. AND
E. Like
F. Order by
G. Group by
H. Null/Not Null
I. Top 2
J. Top 40 Percent
K. Max/Min
L. Count
M. Distinct
N. Avg/Sum
O. IN / NOT IN
P. Between / Not Between
Q. Other Operators
a. <
b. >
c. <> or !=
d. =

Queries

1. Select StudentID, FirstName, LastName, CGPA from students

2. Select * from students where city= 'Lahore'

3. Select StudentID, FirstName, LastName, City from students where City='Lahore'

4. Select * from students where city= 'Lahore' OR city='Faisalabad'

5. Select * from students where StudentID= '5'

6. select * from students where CGPA like '3%'

7. select * from students where City like 'L%'

8. select * from students where City like '%n'

9. Select * from students where City='Multan' And CGPA like '3%'


10. Select * from students order by FirstName

11. Select * from students order by FirstName desc

12. select * from students where CGPA is not null

13. select * from students where CGPA is null

14. select top 4 * from students

15. select top 7 StudentID, FirstName from students

16. Select top 20 percent * from students

17. Select Max(CGPA) as HighestCGPA from students

18. Select Min(CGPA) as LowestCGPA from students

19. select distinct city from students

20. select count(distinct city) from students

21. select count(studentID), city from students group by city

22. select count(StudentID) from students

23. select Avg(StudentID) from students

24. select sum(studentID) from students

25. select * from students where City IN ('Lahore', 'Multan')

26. select * from students where City NOT IN ('Lahore', 'Multan')

27. select * from students where StudentID between 1 AND 4

28. select * from students where StudentID NOT between 3 AND 7

29. select * from students where StudentID < 3

30. select * from students where StudentID > 5

31. select * from students where StudentID != 5


select * from students where StudentID <> 5
32. update students set CGPA='2.12' where FirstName='Khurram'

33. update students set CGPA='2.47' where FirstName='Khurram'

34. Delete from students where studentID='7'

35. Delete from students where Firstname='Hanana'

You might also like