Scribd
Upload
32 views16 pages

Database Assignment No 2

The document describes various SQL queries used to create a database and table, insert data, update records, select records using different criteria, alter the table structure, count records, and delete records. The queries demonstrate how to insert, select, update, alter, count, delete records and use operators like WHERE, BETWEEN, IN, LIKE, AND, OR, and NOT to filter records in a database table.

Uploaded by

Ahsan Sajjad
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
32 views16 pages

Database Assignment No 2

The document describes various SQL queries used to create a database and table, insert data, update records, select records using different criteria, alter the table structure, count records, and delete records. The queries demonstrate how to insert, select, update, alter, count, delete records and use operators like WHERE, BETWEEN, IN, LIKE, AND, OR, and NOT to filter records in a database table.

Uploaded by

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

Database Queries

create database Assignment


use Assignment;
create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary varchar(20),
EmpGrade varchar(5),

Address varchar(500),

);

Select *from Employer

Result
DATA INSERT QUERY

INSERT INTO Employer VALUES (1,'Awais',0305,'IT','10-04-2018',140000,'A','Lahore')


INSERT INTO Employer VALUES (2,'Murtaza',0300,'IT','21-11-2002',140000,'C','Lahore')
INSERT INTO Employer VALUES (3,'Manahil',0312,'elec','12-04-2012',140000,'F','Karachi')
INSERT INTO Employer VALUES (4,'Raza',0321,'DB','13-02-2014',150000,'D','Islamabad')
INSERT INTO Employer VALUES (5,'Ali',0300,'SE','22-12-2007',170000,'B','Faisalabad')
INSERT INTO Employer VALUES (6,'Kashif',0315,'Sec','11-02-2011',20000,'C','Rawalpindi')
INSERT INTO Employer VALUES (7,'Sohail',0345,'CS','01-03-2005',210000,'A','Lahore’)
INSERT INTO Employer VALUES (7,'Sohail',0345,'CS','01-03-2005','21,000','A','Lahore')
INSERT INTO Employer VALUES (8,'Murtaza',0300,'IT','21-11-2002','10,000','C','Lahore')
INSERT INTO Employer VALUES (9,'Ahmad',0311,'DB','15-04-2014','22,000','A','Karachi')
INSERT INTO Employer VALUES (10,'Saqib',0300,'AVI','11-10-2015','33,000','D','Peshawar')
INSERT INTO Employer VALUES (11,'Ali',0321,'SE','22-12-2007','17,000','B','Faisalabad')
INSERT INTO Employer VALUES (12,'Asgher',0315,'AVI','21-11-2017','20,000','C','Rawalpindi')
INSERT INTO Employer VALUES (13,'Khan',0345,'CS','01-03-2005','25,000','A','Lahore')

Select *from Employer

Result
SELECT FROM SPECIFIC COLUMN

select Address from Employer Select salary from Employer select EmpName from Employer
Alter Command
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary int,
EmpGrade varchar(5),
Address varchar(500),

);

alter table Employer add code varchar(10)


alter table Employer add Age varchar(5)
alter table Employer drop column code
UPDATE QUERY COMMAND
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary int,
EmpGrade varchar(5),
Address varchar(500),

);
UPDATE Employer set Address='Quetta' where id=2
UPDATE Employer set EmpGrade='B' where id=7
UPDATE Employer set EmpName='Ahmad' where id=5
Result
SELECT DISTINCT COMMAND
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary varchar(20),
EmpGrade varchar(5),

Address varchar(500),
);

Select DISTINCT EmpGrade from Employer select DISTINCT EmpCell from Employer select DISTINCT Job from Employer
SELECT DISTINCT COMMAND
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary int,
EmpGrade varchar(5),
Address varchar(500),

);

Select COUNT(*) from Employer


Select COUNT(*) from Employe where EmpCell=0300
Select COUNT(*) from Employer where EmpGrade=‘A’
SELECT COUNT COMMAND
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary int,
EmpGrade varchar(5),
Address varchar(500),

);

select EmpCell from Employer where EmpCell=0300


select Job from Employer where Job=‘IT’
select Address from Employer where Address='Lahore'
AND OPERATOR COMMAND
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary int,
EmpGrade varchar(5),
Address varchar(500),

);

Select EmpName,EmpGrade,Address from Employer where EmpGrade='A'AND Address='Lahore'


Select EmpName,salary,EmpGrade from Employer where salary='20,000'AND EmpGrade='C'
Select EmpName,Job,Address from Employer where Job='IT'AND Address='Lahore'
OR OPERATOR COMMAND
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary varchar(20),
EmpGrade varchar(5),

Address varchar(500),

);

Select EmpName,Address from Employer where Address='Lahore' OR Address='Karachi'


Select EmpName,EmpGrade from Employer where EmpGrade='A' OR EmpGrade='B'
Select EmpName,Job from Employer where Job='IT' OR Address='CS'
NOT OPERATOR COMMAND
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary int,
EmpGrade varchar(5),
Address varchar(500),

);

Select EmpGrade From Employer where NOT EmpGrade= 'F';


Select Address From Employer where NOT Address= 'karachi';
Select Job From Employer where NOT Job= 'IT';
BETWEEN OPERATOR COMMAND
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary int,
EmpGrade varchar(5),
Address varchar(500),

);
Select *from Employer where salary Between '10,000' AND '20,000'
Select *from Employer where EmpGrade Between 'A' AND 'B'
HIREDATE OPERATOR COMMAND
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary varchar(20),
EmpGrade varchar(5),

Address varchar(500),

);

Select *From Employer where hireDate between '11-10-2015,DD/MM/YYYY' AND '22-12-


2007,DD/MM/YYYY';
IN OPERATOR COMMAND
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary int,
EmpGrade varchar(5),
Address varchar(500),

);

Select *From Employer where Address IN ('Lahore','Karachi')


Select *From Employer where EmpGrade IN ('A','B')
Select *From Employer where salary IN ('15,000','20,000')
LIKE OPERATOR COMMAND
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary varchar(20),
EmpGrade varchar(5),

Address varchar(500),

);
Select *From Employer where EmpName Like 'A%'
Select *From Employer where EmpName Like '%D'
Select *From Employer where EmpName Like 'K%'
DELETE RECORD COMMAND
create database Assignment
use Assignment;

create table Employer

(
id int primary key,
EmpName varchar(30),
EmpCell int,
Job varchar(20),
HireDate varchar(10),
salary int,
EmpGrade varchar(5),
Address varchar(500),

);
Delete From Employer where id=1;
Delete From Employer where salary='20,000';
Delete From Employer where EmpGrade='D';

You might also like