//dashboard open

localhost:8080/phpmyadmin/

Lab#05

//employee
create table employee (
e_id int,
e_name varchar(255),
department varchar,
age int,
gender varchar,
email varchar

);

//department
create table dept (
d_id int,
d_name varchar(255),
university varchar(255),
email varchar(255)

);

//new_dep
create table new_dep(
d_id int not null,
d_name varchar(255) DEFAULT 'Computer Science',
loc varchar(234)

);

LAb #06

// Constraints
s_name varchar(220) not null //-> (constrain)

Differnce btw unique and primary key:

student
========
id (Unique)
name
email (Unique)
password (Unique)
cnic (Unique)

Note: ik table ma unique keys ziyada ban skti han lakin primary key 1 hi hoti ha

Unique key and primary key data dublicate na ho isi liya use krty han.

// for madify change data type

alter table student
modify column address varchar(233)

//for drop
alter table student
drop column address

//for change name of column

alter table student
change column product name ->column name product_name -> jo name rakhna ha
varchar(90)

//for change table name

alter table student
Rename to student table -> jo name likhna ha vo ay ga

//syntax of foreign key

CREATE TABLE project(

p_id int PRIMARY key,

p_name varchar(234),
s_id int, FOREIGN key (s_id) REFERENCES student_table (s_id),
supervisor varchar(90)

);

LAB# 07:

//Table of employee

CREATE TABLE Employee(

e_id int() PRIMARY Key,
e_name varchar(230),
city varchar(230),
d_id int() , FOREIGN key (d_id) REFERENCES department(d_id)
);

//Table of Department

Create TABLE department(

d_id int PRIMARY key,
d_name varchar(230),
hod varchar(400)
);

//Syntax

insert into department (d_id , d_name , hod) values (1, 'CS', '[Link]'), (2,
'HR', 'Faiza'), (3, 'EE', 'Humble Hassan'), (4, 'Bussiness', '[Link]'), (5,
'SE', '[Link]');
INSERT into employee (e_id , e_name , city , d_id) VALUES (101, 'Ahmad', 'Sialkot',
1), (102, 'Arslan', 'Gujranwala', 2), (103, 'Hamza', 'Lahore', 1), (104, 'Ali',
'Sialkot', 3), (105, 'Haider', 'Gujranwala', 5);

//updation syntax
update employee set e_name = 'Ali Ahmad' where e_name = 'Ali'
//delete row syatax:
delete from department where d_name = 'cs' =========== ya d_id dlt ho ga d_name nai
bcz vo inter connect tha
delete from department where d_id = 4; ============== ya dlt ho ga bcz vo inter
connect nai tha

//delete data from employee department

delete from employee where e_name = 'Ahmad';

//Sub Queiry ===== // agar data bhi check krna ha

Delete from employee where d_id = (Select d_id From department where d_name =
"CS");

Delete from employee where d_id = 1

//auto implement ======== jesay jesay data enter hota jay ga vo us ko us ki values
assign krta jay ga
create table student(
s_id int AUTO_Increment primary key,
s_name varchar(222),
department_name varchar(456)

);

//add data
auto_increment = 100; ======= agar ap na apni marzi ki kisi value sa start krna ha
insert into student(e_name, dept_name) values (hamza, cs),(ali,se),(umer,ee);

//where close
delete from employee where d_id = (Select d_id from dept where d_name = "CSS");

//Selection
is used to select the data of rows and coloumns

Lab# 08

//how to import file

sab sa pehly import pa jana ha fir choose file krna ha fir import kr dena ha

//*
means ka ik table ma jitny bhi coloumns h

//Selection
is used to select the data of rows and coloumns

//Selection Syntax:

Select * from tablename

//agar department ka coloum chng krny hn

Select * from dept

//agar ham bs ik coloumn chng krna ha ha to bs us ka name likhy gay

Select d_name from dept
 // How to use operations
SELECT EMPNO , ENAME, SAL , SAL+100 from emp;

ya jo +100 ha vo bs display krwany ka liya hova ha actual nai hova

// agar ham na sal+100 ko temporary name dena ha

SELECT EMPNO , ENAME, SAL , SAL+100 as "After Increment" from emp;
====================== is ma agar as use krna ha to thkh ha vrna asay bhi chalay ga

SELECT EMPNO , ENAME, SAL , SAL-100 as "After Increment" from emp;

========================= used for subtraction
SELECT EMPNO , ENAME, SAL , SAL+100 as "After Increment" from emp;
========================= used for addition

// Concatination
SELECT concat (ENAME , JOB) from emp ================================= do ya do sa
zyada coloum ko merge krny ka liya =========== e.g SMITHCLERK============ smith is
ename and clerk is job

// actual use of concat

SELECT concat ( 'Employee' ,ENAME , ' work as a ' ,JOB ) from emp;
SELECT concat ( 'Employee' ,ENAME , ' joined the company on ' ,HIREDATE) from emp;
===== it is written as EmployeeSMITH joined the company on 1980-12-17

// is ma vo data display krwaya ha jis ma employes sales man ha

SELECT ENAME , JOB from emp WHERE JOB = "Salesman";
// different methods
SELECT ENAME , DEPTNO from emp WHERE DEPTNO = "10";
SELECT ENAME , JOB, DEPTNO from emp WHERE DEPTNO = "10";

// queiry: empno , ename , job of employe SCOTT

Syntax-> SELECT ENAME , JOB, DEPTNO from emp WHERE ENAME = "SCOTT";

// TO FIND SPECIFICK HEIREDATE

SYNTAX-> SELECT * FROM emp WHERE HIREDATE = '1980-12-17'; ======= YA US EMPLOYEE KA
DATA DISPLAY HOVA HA JO IS DATE KO HEIRE HOVA THA

//IN (set) ========= un value ka data display kry ga jis ko ham na set kiya ho ga
SELECT EMPNO, ENAME, SAL, DEPTNO, MGR FROM emp WHERE MGR IN (7698, 7566, 7782);
SELECT EMPNO, ENAME, SAL, DEPTNO, ENAME FROM emp WHERE ENAME IN ('King', 'Smith');

//NOt In (set) ========= un values ka data display nai ho ga jo ham na set kiya ho
ga
SELECT EMPNO, ENAME, SAL, DEPTNO, MGR FROM emp WHERE MGR NOT IN (7698, 7566,
7782);
SELECT EMPNO, ENAME, SAL, DEPTNO, MGR FROM emp WHERE ENAME NOT IN ('King',
'Smith');

//check kry gay jis ki salary <=3000 ha

SELECT EMPNO, ENAME, JOB, SAL FROM emp WHERE SAL <=3000;

// check jin ki salary 2500 sa 3500 ka darmiyan ma ha

SELECT ENAME, SAL FROM emp WHERE SAL BETWEEN 2500 AND 3500;

//String WildCards =========== ma ham log patterns ko match krty hn

//Like ===== use hota h ka agar ham na starting alphabet khud dena ha jesay hamay
name chahiya jo A sa start hota ho oe agay % is waja sa lagaya ha aagay jo mrzi ay
SELECT ENAME from emp WHERE ENAME LIKE 'A%';
Syntax-> 'A%' ;

//%ham na bad ma is liya use kiya ha ka jis ka end ma n aata ho

SELECT ENAME from emp WHERE ENAME LIKE '%N';
Syntax-> '%N';

//'%on%' is liya use kiya ha ka ya kahi start ma mid ma end pa jaha ay us ka data
show ho
SELECT ENAME from emp WHERE ENAME LIKE '%on%';
Syntax-> '%on%' ;

// _ ya is liya ka is ka 2 word a ho
SELECT ENAME from emp WHERE ENAME LIKE '_a%';

//lab#09

agar ham na multiple tables ka data select krna ha

select e_name, d_name from emp,dept

// Joins
2 table ko appas ma join krna
// is sa data dublicate ho ga table cross product ho gay
SELECT employee.d_id , employee.e_name , department.d_name , department.d_id from
employee,department

//ya data dublication ko rokti ha

SELECT employee.d_id , employee.e_name , department.d_name , department.d_id from
employee,department where employee.d_id = department.d_id;

//crossproduct + condition = join ka liya

SELECT employee.d_id , employee.e_name , department.d_name , department.d_id from
employee NATURAL JOIN department

join hamesha related table ko krna h

// Difference btw Natural join and join

Natural join ========== ma us ko pata ho ga us na kon sa table ko relate krna h

SELECT employee.d_id , employee.e_name , department.d_name , department.d_id from
employee NATURAL JOIN department

Join ========== is ma hamay batana paray ga agay condition likhni paray gi

Syntax for join === SELECT employee.d_id , employee.e_name , department.d_name ,
department.d_id from employee JOIN department ON employee.d_id=department.d_id;

SELECT employee.e_name , department.d_name, [Link] from employee JOIN

department ON employee.d_id=department.d_id;

//on === tab use hota ha jab hamara coloumn name different hota ha
//using ===== jab coloumn name same hota ha

//Single row function

is ma data single single row ka pata lgta ha
//multiple row function
is ma ham log set ko rows ka data define krty ha

//Select
jab bhi koi function use krna ha to pehly select word use krna ha

//agar lowers letters sa likhy words nikalny ha ka kon kon sa ha

SELECT lower (ENAME) FROM emp;

//ik hi function ka andr 2 function us ma upper or lower bataya ha

SELECT concat('Employee' , lower(ENAME) , 'work as a' , upper(job)) FROM emp WHERE
ENAME = "Smith";

//Substr
SELECT substr('Hello World' , 2,6) ======= ya ham use krty han ka ham na kaha sa
kaha tk print krwana ha
-> ello W // output is ma us na spce bhi count ki ha
-> elloWo // is ma us na space nai count ki bcz is ka syantax ma space nai thi

//instr ==== is ki specific word ki position find krny ka liya

SELECT instr('HelloWorld' , 'O') the output is 5

//LPAD
SELECT LPAD (SAL,10,'*')FROM emp;

===================================================================================
===================================================================================
=====================================================
===================================================================================
===================================================================================
=====================================================

+======+
| LAB 7|
+======+

==========================+
->syntax for foreign Key: |
==========================+
CREATE TABLE department(

d_id int PRIMARY KEY,

d_name varchar(255),
hod varchar(255)
);

CREATE TABLE Employee(

e_id int PRIMARY KEY,

city varchar(255),
d_id int, FOREIGN KEY(d_id) REFERENCES department(d_id)
e_name varchar(255)
);

=========================+
->Syntax for adding data:|
=========================+

insert into department (d_id , d_name ,HoD)

VALUES(1 , 'CS' ,'[Link]'),
(2 , 'HR' ,'[Link]'),
(3 , 'Buisness' ,'[Link]'),
(4 , 'SE' ,'[Link]');

//write values in order of the column and write varchar values in sigle quotation.

insert into employee (e_id,city, d_id, e_name) VALUES

(101 ,'Gujranwala',1, 'Subhan'),
(102 ,'Los angeles',2, 'Mahad butt'),
(103 ,'Sialkot',1, 'Ahmed' ),
(104 ,'lahore',4, 'Ali');

//More than one employee can work in a single department

==========================+
->For updating the values:|
==========================+

UPDATE employee set e_name = 'Ali ahmed' where e_name = 'Ali';

UPDATE employee set e_name = 'Hamza' where city = 'lahore';

// jis column ko update krna hai uska naam set name mai rkhna hai
====================+
->For deleting rows:|
====================+

DELETE FROM department where d_id = 3;

DELETE FROM employee where e_name = 'Hamza';

DELETE FROM employee WHERE d_id = (SELECT d_id FROM department WHERE d_name =
'CS');
|
this means
|
DELETE FROM employee WHERE d_id =1

// The data that is not used in other table can be deleted. Error other wise.

=============================+
--> for primary and foreign key after making table |
=============================+
ALTER TABLE employee
ADD CONSTRAINT pk_employee PRIMARY KEY (emp_id);

ALTER TABLE employee ADD FOREIGN KEY (emp_id) REFERENCES employee (manager_id);
+======+
|LAB 8 |
+======+

+=====================+
->For Selecting: |
+=====================+

SELECT * FROM `emp`

// display all the coulumns in the table because of "*"

SELECT e_name FROM `emp`

//display only the name column

SELECT ENAME,JOB FROM `emp`;

SELECT ENAME,JOB,SAL FROM `emp`;

SELECT ENAME,EMPNO,SAL, SAL+100 FROM `emp`;

//First SAl will display the table second SAL will incrimant the salary by 100 in
SAl it's temporary and will discard the changes when refreshed.

SELECT ENAME,EMPNO,SAL, SAL+100 AS "AFTER INCRIMANT" FROM `emp`;

//This will add another column named AFTER INCRIMANT that will display values after
+100 this is also temporary.

SELECT ENAME,EMPNO,SAL, SAL-200 AS "AFTER DECRIMANT" FROM `emp`;

//This will add another column named AFTER DECRIMANT that will display values after
-200 this is also temporary.

---NOTE--- **AS keyword is not necessary**

SELECT concat(ENAME , JOB) FROM emp;

SELECT concat(ENAME,JOB,EMPNO) FROM `emp`;

//concat the columns. Concating is used for making a Proper Statement as shown
below -->=

SELECT concat('Employee' , ENAME, 'Work as a' ,JOB,'his number is = ' ,EMPNO) FROM
`emp`;

----*A sentence will be generated as = Employee Smith Work as a Clerk his number is
= 102*----

SELECT concat('Employee ' , ENAME, ' Work as a ' ,JOB,' his number is = ' ,EMPNO)
AS "CONCAT" FROM `emp`;

//This will give a name "CONCAT" to the whole column.

SELECT ENAME,JOB FROM emp WHERE JOB = "salesman"

//Display name and job of only employees that are working as salesman.
SELECT ENAME,JOB,DEPTNO FROM emp WHERE DEPTNO = 10

//Display name,department number and job of only employees whose deptno is 10.

SELECT ENAME,JOB,EMPNO FROM emp WHERE ENAME = "scott";

SELECT ENAME,JOB,EMPNO,SAL FROM emp WHERE SAL <= 3000;

//Display name,department number,salary and job of only those employees whose

salary is less than and equals to 3000.

SELECT ENAME,JOB,EMPNO,SAL FROM emp WHERE SAL BETWEEN 2500 AND 3500;

//Display name,department number,salary and job of only those employees whose

salary b/w 2500 to 3000.

+=====+
IN = |
+=====+

SELECT ENAME,JOB,EMPNO,SAL FROM emp WHERE MGR IN (7902,7698,7839);

//Display name,department number,salary and job of only those employees whose

manager id is 7902,7698 and 7839.

+=========+
NOT IN = |
+=========+

SELECT ENAME,JOB,EMPNO,SAL FROM emp WHERE MGR NOT IN (7902,7698,7839);

//Display name,department number,salary and job of all employees expect

7902,7698,7839.

-----------------------------------------------------------------------------------
--------------------------------------------------------->

SELECT ENAME,JOB,EMPNO,SAL FROM emp WHERE ENAME IN ('KING','SMITH');

SELECT ENAME,JOB,EMPNO,SAL FROM emp WHERE ENAME NOT IN ('KING','SMITH');

+-------------+
MODULOUS(%)= |
+-------------+

SELECT ENAME FROM `emp` WHERE ENAME LIKE 'S%'

SELECT ENAME FROM `emp` WHERE ENAME LIKE 'A%'

//Display name of employees whose STARTING alphabet is S , A

SELECT ENAME FROM `emp` WHERE ENAME LIKE '%S'

//Display name of employees whose ENDING alphabet is S.

SELECT ENAME FROM `emp` WHERE ENAME LIKE '%ON%';

//Display name of employees who have O and N alphabet togethher in their names.

SELECT ENAME,JOB FROM `emp` WHERE JOB LIKE '%MAN%';

//Display name of employees and Job who have M,A,N alphabet togethher in their
Job's name.

+---------------+
UNDERSCORE(_)= |
+---------------+

SELECT ENAME FROM `emp` WHERE ENAME LIKE 'S__T_';

//underscore used for the missing alphabets. this will display name of employee
SMITH and SCOTT.

+===================+
| LAB 9 |
+===================+

+=====================================================+
->For Selecting different values from different table:|
+=====================================================+

SELECT employee.d_id ,employee.e_name , department.d_name , department.d_id from

employee , department
//duplicate values.

SELECT employee.d_id ,employee.e_name , department.d_name , department.d_id from

employee , department WHERE employee.d_id * department.d_id;
//Duplicate values.

SELECT employee.d_id ,employee.e_name , department.d_name , department.d_id from

employee , department WHERE employee.d_id = department.d_id;
//No duplicate values.
OR

+===============+
| NATURAL JOIN: |
+===============+
SELECT employee.d_id ,employee.e_name , department.d_name , department.d_id from
employee NATURAL JOIN department;
//both will give same output.

+=======+
| JOIN: |
+=======+
SELECT employee.d_id ,employee.e_name , department.d_name , department.d_id from
employee JOIN department ON employee.d_id = department.d_id;
//we need to give the condition. The output is same

SELECT [Link] ,[Link] , [Link],[Link] from emp JOIN dept ON [Link]

= [Link];
//from lab 8

SELECT [Link] ,[Link] , [Link],[Link] from emp JOIN dept ON [Link]

= [Link] WHERE ENAME = "SCOTT";

SELECT [Link] ,[Link] , [Link],[Link] from emp JOIN dept ON [Link]

= [Link] WHERE [Link] = 20;

+===========+
| LAB 10,11 |
+===========+

SELECT e.e_name,d.d_name FROM employee e JOIN department d ON e.d_id = d.d_id;

//(here e after employee is the temporary name given to the employee table and d to
department table.
this will display and join the coulumns e_name and d_name where d_id of both tables
are same)

CREATE TABLE employee( emp_id int, emp_name varchar(255), manager_id int );

SELECT e.last_name employees, m.last_name mgr FROM employees e JOIN employees m ON

(e.MANAGER_ID = m.EMPLOYEE_ID);
//from schema

SELECT EMPLOYEE_ID , CITY , DEPARTMENT_NAME FROM employees e JOIN departments d ON

d.DEPARTMENT_ID = e.DEPARTMENT_ID JOIN locations l ON d.LOCATION_ID =
l.LOCATION_ID;

===========
left outer =
===========

SELECT e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM employees e LEFT OUTER

JOIN departments d ON (e.DEPARTMENT_ID = d.DEPARTMENT_ID);

===========
right outer =
===========
SELECT e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM employees e RIGHT OUTER
JOIN departments d ON (e.DEPARTMENT_ID = d.DEPARTMENT_ID);

==========+
LAB 12 = |
==========+

SELECT lower(ENAME) FROM emp;

//to display in lower case. lower is the function name.
SELECT concat('Employee ' , lower(ENAME), ' Work as a ', upper(job) ) AS "CONCAT"
FROM `emp`;
//this will display Employee "employee's name in lower case" work as a "employee's
job in upper case". The word written in inverted comma after as will be the name of
column. Here concat will be name of column.
//e.g= name is smith and job is clerk it will display = "Employee smith work as a
CLERK"

SELECT concat('Employee ' , lower(ENAME), ' Work as a ', upper(job) ) FROM emp
WHERE ename = "Smith";
//AS keyword id not necessary.

---------
SUBSTR =
---------
SELECT substr('Helloworld',1,6)
//1 is the starting position and 6 is the ending position that how many words are
to be [Link] where to where.
SELECT substr('Helloworld', 5 , 8 );
SELECT SUBSTRING('DatabaseSystems' , 5, 4) AS Result;
// Name after As will be the name of the column.

---------
LENGTH =
---------
SELECT length('EBAAD ALI' );
//it will display length of the whole statement including spaces.

---------
INSTR =
---------
SELECT instr('HelloWorld','W');
//display the position of the specific word in numbers. e.g W is on number 6 so "6"
will be displayed.
SELECT INSTR(LOWER('Programming'), 'g') AS position;

-----------------
LPAD(left pad) =
-----------------
SELECT lpad(SAL,10,'*') FROM emp;
//this will replace empty spaces with "*" e.g = salary is 8000 it will be displayed
as = ******8000, completing 10 values that we gave it , SAL here is the column in
emp table.

-----------------
RPAD(right pad) =
-----------------
SELECT Rpad(SAL,10,'*') FROM emp;
//this will replace empty spaces with "*" e.g = salary is 8000 it will be displayed
as = 8000******, completing 10 values that we gave it , SAL here is the column in
emp table.

---------
REPLACE =
---------
SELECT REPLACE('JACK and JUE' , 'J' ,'BL');
// it will replace J with BL giving output = black and blue.
---------
trim =
---------
SELECT trim('H' FROM 'HelloWOrld');
display everything except H.

============+
Lab 14 |
============+

SELECT AVG(SAL), MAX(SAL), MIN(SAL), SUM(SAL) FROM emp;

OUTPUT WILL BE =
---------------------------------------------------------
AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)
2073.214286 5000.00 800.00 29025.00
---------------------------------------------------------

SELECT AVG(SAL), MAX(SAL), MIN(SAL), SUM(SAL) FROM emp WHERE JOB LIKE '%MAN';
//This will display Salary of Employees whose job is ending with "MAN"

SELECT AVG(DISTINCT SAL) FROM emp;

// NO duplicate values when we use distinct keyword

-------+
COUNT =
-------+
SELECT COUNT(SAL) FROM emp;
// count no. of salaries

SELECT COUNT(SAL) FROM emp WHERE DEPTNO = 10;

//display salaries of employees whose department [Link] 10.

SELECT DEPTNO, AVG(SAL) FROM emp GROUP BY DEPTNO;

=========+
Last Lab |
=========+
--------
views= |
--------
CREATE VIEW empView1 AS SELECT EMPLOYEE_ID, FIRST_NAME, EMAIL,SALARY FROM employees
//here empView1 is view name.

CREATE VIEW salView2 AS SELECT ENAME, EMPNO, SAL FROM emp WHERE SAL>1000;
//condition applied

DROP VIEW salview2;

//to delete View.
CREATE VIEW view3 AS SELECT SAL,EMPNO,ENAME FROM emp WHERE SAL > 5000 AND ENAME
LIKE '%ee%';
//two conditions using AND.

we use "ALTER VIEW view3" to make changes