PHP Notes By Kshitij :: PART 8

Structured Query Language   ( SQL )

 

Create Database : college

1) Create Table : students

Fields :

id(int, auto increment, primary key, starts from 1)

fname (varchar 30)

lname (varchar 30)

city (varchar 20)

dob (date)

gender (enum – “Male”, “Female”)

 

2) Create Table : course

Fields :

id (primary key, auto number, Starts from 101)

name (varchar 30)

duration (varchar 10)

fees (int)

status (enum – “Active”, “Inactive”)

 

3) Create Table : registration

Fields:

id (int, auto increment, starts from 501)

sid (int)

cid (int)

rdate (timestamp)

status (enum – “Active”, “Inactive”)

 

4) Create Table : fees

Fields :

id (primary key, auto number)

sid (int)

cid (int)

amount (int)

dod (timestamp)

 

 

 

 

What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulate databases

 

What Can SQL do?

  • SQL can create new databases
  • SQL can create new tables in a database
  • SQL can insert records in a database
  • SQL can update records in a database
  • SQL can delete records from a database
  • SQL can execute queries against a database
  • SQL can retrieve data from a database

 

 

DDL: Data Definition Language

Structure of Database is created, manipulated and deleted

Commands ==> create, alter and drop

 

DML: Data Manipulation language

Commands ==>Insert, Update, truncate and delete

 

DCL: Data Control Language

 

A data control language (DCL) is a syntax similar to a computer programming language used to control access to data stored in a database. In particular, it is a component of Structured Query Language (SQL).

 

What is the difference between SQL and MySQL or SQL Server?

SQL or Structured Query Language is a language; language that communicates with a relational database thus providing ways of manipulating and creating databases. MySQL and Microsoft’s SQL Server both are relational database management systems that use SQL as their standard relational database language.

 

Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. “student” or “faculty”). Tables contain records (rows) with data.

 

primary key >> unique + not NULL  ==> is called constant (means validation of fields based on SQL)

 

one table’s primary key is used in another table as foreign key

in a table second primary key can be created using UNIQUE + NOT NULL

 

How can create database

create database <name of database>

create database college

___________________________________

 

how can create table in database

create table <name of table>(<fields…>)

create table faculty(id int primary key auto_increment, name varchar(20), ctcnbrvarchar(10))

____________________________________________________

 

INSERT INTO tablename[(field1, field2,…)] values(value1, value2,…)

 

insert into students(fname, lname, city, dob, gender) values(‘Naved’, Khan’, ‘Jaipur’, ‘1991-06-23’, ‘Male’)

 

insert into students values(null, ‘Deepak’,’Sharma’, ‘Ajmer’, ‘1991-04-23’, ‘Male’)

 

insert into students(fname, lname, city, dob, gender) values(‘Vikas’, ‘Jain’, ‘Ajmer’, ‘1990-11-10’, ‘Male’), (‘Anita’, ‘Sharma’, ‘Kota’, ‘1990-11-10’, ‘Male’), (‘Akshya’, ‘Kumar’, ‘Kota’, ‘1990-11-10’, ‘Male’)

 

 

 

 

UPDATE tableName set field1=value, field2=value,… [where expression]

update students set city=’Ajmer’ where id=3

update students set fname=’Priyanka’ where id=5

update students set city=’Jaipur’ where id>5 and id<15

update student set fname=” where id =25

ALTER TABLE `student` ADD `contact` VARCHAR( 10 ) NOT NULL

 

Removing Records:

delete from TableName [where expression]

delete from students where id = 7

 

truncate table tableName   //it deletes all the records from table and start index with 1

truncate table student          // but it will not delete the structure of table

 

Select –>The SELECT statement is used to select data from a table.

 

SELECT fieldName1, fieldName2,… FROM <tablename>

[WHERE expr]

[Group By <fieldName>]

[Having <expr>]

[Order By <fieldName> [asc/desc]]

[Limit <recordno>]

 

 

SELECT * FROM students

SELECT fname, lname, city from students

select * from students where gender = “female”

 

select fname, lname, city, gender from students where gender=’Female’ or city = “ajmer”

 

Wild Card Characters

_ ->Any Single Character Only Single Length

% -> All Characters Any Number of Length

 

like

select * from students where fname like “_____”

select * from students where fname like “K____”

select * from students where fname like “%i”

select * from students where fname like “sa%”

select * from students where fname like “%sa%”

select * from studentswhere fname like “%ar%” or gender like “%em%”

 

 

 

 

Expression Query

select fname, lname, salary, salary * 12 from faculty

select*, salary * 12 from faculty

Alias: Giving a new name to field or table.

select name, dept, salary, salary * 12 as AnnualSalary from faculty

select name, dept, salary, salary * 12 as “AnnualSalary” from faculty

select name, dept, salary, salary/100*10 as HRA  from faculty

select name, dept, salary, salary*0.10 as HRA, salary+salary*0.10 as NetSalary from faculty

 

SQL Server

select name, salary, salary * 12 as “Annual Salary” from faculty

select name, salary, salary * 12 as “Annual Salary” from faculty where (salary * 12) > 150000

 

select name, dept, salary, salary*0.10 as HRA, salary+salary*0.10 as NetSalary from faculty where salary+salary*0.10>10000

 

Order By

select * from students order by id desc

select * from students order by fname

select * from students order by city, fname

select * from students order by city, fnamedesc

select * from students order by citydesc, fname desc

 

Limit

select * from students limit 5

select * from students limit 3, 7

 

Aggregate Queries

Sum

Max

Min

Avg

Count

 

select sum(amount) as TotalAmount from fees

select max(amount) as MaximumAmount from fees

select max(salary) as MaxumumSalary from faculty

select count(id) as total_records from faculty

select sum(salary) as total_salary from faculty

selectavg(salary) as avg_salary from faculty

 

Functions

  1. concat: Concate 2 or more fields

selectconcat(fname, ” – “, city) as Address from students

select concat(id, ” => “, fname, “- “, city) as Address from students

 

  1. in

select * from students where id = 301 or id = 305 or id = 309

select * from students where id in(301, 302, 303, 309, 313)

select * from course where name in(‘Java’, ‘PHP’, ‘VB’)

select * from course where name not in(‘Java’, ‘PHP’, ‘VB’)

 

  1. between

select * from students where id >= 309 and id <= 320

select * from students where id between 309 and 320

select * from student where id not between 9 and 20

 

  1. Day(date)

select * from students where day(dob)=22

 

  1. Month(date)

select* from students where month(dob)=4

 

  1. Year(Date)

select * from students where year(dob)=1999

 

select dob, day(dob) as Days, month(dob) as Months, year(dob) as Years from students

 

  1. current_date returns systems date

selectcurrent_date

 

  1. current_times: returns System’s Current Time

selectcurrent_time

 

selectcurrent_timestamp    //it will display date and time

  1. UPPER(), LOWER(), NOW(), CURTIME(), CURDATE()

select UPPER(name) from students

select lower(name) from students

select now()

selectcurtime()

selectcurdate()

 

  1. LEFT, RIGHT, MID

select left(name, 4) from students, right(name, 4), mid(name, 3,6) from students

 

Group By Query

select  sid, sum(amount) from fees group by sid  //it will arrange data after grouping by sid

 

selectdept, sum(salary) as total_salary from faculty group by dept

 

select city, count(city) from students group by city

 

Error will come in below command (we cannot join aggregate query with where clause)

selectdept, sum(salary) as “Total Salary” from faculty group by dept where sum(salary)>150000

 

 

 

 

Having Clause

 

  1. We can’t use aggregate functions after where clause.
  2. where works fast than having.
  3. where loads only those records which match the criteria while having loads all the records in memory then display those records which match the criteria.

 

selectdept, sum(salary) as total_salary from faculty group by depthaving sum(salary) > 60000

 

selectdept, sum(salary) as total_salary from faculty group bydepthavingdept = “Accounts” or dept = “Marketing”

 

select dept, min(salary), max( salary ) , sum( salary ) FROM faculty group by dept

 

selectdept, sum(salary) as total_salary from faculty group by dept order by total_salarydesc

 

selectdept, sum(salary) as total_salary from faculty group by dept order by total_salarydesc limit 2

 

select students.id, students.fname, students.lname,sum(fees.amount) from students, fees where  students.id=fees.sid group by students.id order by sum(fees.amount) desc limit 1

 

Nested Query

select * from faculty where salary = (select max(salary) from faculty)

select * from fees where amount = (select max(amount) from fees)

select id, name, salary from faculty where salary =(select max(salary) from faculty)

 

Join Tables

 

Display All Records

 

select table1.fieldname, table2.fieldname,… from table1, table2 where table1.commonfield = table2.commonfield

 

SELECT * from students join fees on students.id=fees.sid

 

select students.id, students.fname, students.lname, students.city, fees.id as ReceiptNo, fees.amount, fees.dod from students, fees where students.id=fees.sid

 

select students.id, students.fname, students.lname, students.city, sum(fees.amount) as TotalFees, fees.dod from students, fees where students.id=fees.sid group by students.id

 

OR

 

select table1.fieldname, table2.fieldname from table1 INNER JOIN table2 ON table1.commonfield = table2.commonfield

 

select students.id, students.fname, students.lname, students.city, sum(fees.amount) as TotalFees from students inner join fees on students.id=fees.sid group by students.id

 

 

select course.name as “Course Name”, registration.dor as “Date of Registration”, course.fees as “Course Fees” from course, registration where course.id=registration.cid and registration.sid=301

 

 

select students.id, students.fname, students.lname, students.city, fees.amount, fees.dod from students, fees where   students.id=fees.sid and (students.id=1000)

 

select students.id, students.fname, students.lname,students.city, fees.amount, fees.dod from students, fees where students.id=fees.sid and (students.id=1000 or students.id=1001)

 

 

select students.id, students.fname, students.lname,sum(fees.amount) from students, fees where  students.id=fees.sid group by students.id

 

 

select students.id, students.fname, students.lname,sum(fees.amount) from students, fees where  students.id=fees.sid and(students.id=1000) group by students.id

select students.id, students.fname, students.lname,fees.amount, fees.dod from students, fees where students.id = fees.sid and fees.amount>= 500

select students.id, students.fname, students.lname,fees.amount, fees.dod from students, fees where students.id = fees.sid and students.name like ‘m%’

select students.id, students.fname, students.lname,fees.amount, fees.dod from students INNER JOIN fees ON students.id = fees.sid where students.name like ‘m%’

 

Joining 3 tables

SELECT students.fname, students.lname,students.city, fees.amount, fees.dod, course.name, course.duration FROM students INNER JOIN fees on students.id = fees.sid INNER JOIN course on course.id = fees.cid

 

select fees.id as “Fees Id”, students.id as “Student Id”, students.fname, students.lname,course.id as “Course Id”, course.name as “Course Name”, fees.amount, fees.dod as “Date of Deposit” from students inner join fees on students.id=fees.sid inner join course on course.id=fees.cid

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

LEFT OUTER JOIN

 

course                                 students

 

 

 

 

 

 

 

 

 

 

 

select course.cid, course.cname, course.fees, students.sid, students.sname, students.gender from course left outer join students on course.cid=students.cid

 

 

 

 

 

 

 

 

 

 

 

 

select students.sid, students.sname, students.gender, course.cid, course.cname, course.fees  from students left outer join course on  students.cid=course.cid

 

 

 

 

 

 

 

 

RIGHT OUTER JOIN

 

select students.sid, students.sname, students.gender, course.cid, course.cname, course.fees  from students right outer join course on  students.cid=course.cid

 

select course.cid, course.cname, course.fees, students.sid, students.sname, students.gender  from course right outer join students on  course.cid=students.cid

 

 

 

 

 

SELECT fees.fid AS “Fees Id”, students.sid AS “Student Id”, students.sname, fees.amount, fees.dod AS “Date of Deposit” FROM fees RIGHT OUTER JOIN students ON fees.sid = students.sid

 

 

 

 

 

 

 

 

The SQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.

There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records.

Syntax:

The basic syntax of DISTINCT keyword to eliminate duplicate records is as follows:

SELECT DISTINCT column1, column2,…..columnN FROM table_name

WHERE [condition]

 

SELECT DISTINCT City FROM students

 

 

Assignments

1) Display all students where Date of Birth year is 1990.

2) Display name, coursenameand fee where course status is Active.

3) Display all the information where student has selected course PHP

4) Update city to Ajmer where city equals to Jaipur.

5) Insert two records in student table.

6) Delete record where city equals to blank.

Assignments

01) Add gender field in student default value should be male.

02) Drop the primary key from student.

03) Make id field of student table again primary.

04) Display all the students who are registered for courses java and php. 10003 10015

05) Display all the students who are registered for more than two courses.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *