What is SQL?
SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving
data stored in a relational database.
SQL is the standard language for Relational Database System. All the Relational Database Management
Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL
History
1970 – Dr. Edgar F. “Ted” Codd described a relational model for databases.
1974 – Structured Query Language appeared.
1978 – IBM released a product called System/R.
1986 – IBM developed the prototype of a relational database, which is standardized by ANSI.
1989 – First ever version launched of SQL
1999 – SQL 3 launched with features like triggers, object-orientation, etc.
SQL 2003 – window functions, XML-related features, etc.
SQL 2006 – Support for XML Query Language
Features
• Client-server execution and remote database access – SQL commands can control how a client
application is allowed to access the database remotely.
• Security and authentication – SQL can make sure that only specific details of the database are
visible to the user while the entire database is secured by DBMS.
• Embedded SQL – SQL can embed languages like COBOL, C, Java, etc. to query at runtime
• Transaction Control Language – TCL is used to control the transactions
What is database?
A database is a well-organized collection of data that is stored in an electronic format. To be more
specific, a SQL database is an electronic system that allows users to easily access, manipulate, and
update the data
SQL commands are traditionally divided into four categories:
• Data Query Language (DQL Commands in SQL)
• Data Definition Language (DDL Commands in SQL)
• Data Manipulation Language (DML Commands in SQL)
• Data Control Language (DCL Commands in SQL)
SQL data types—
• Numeric – The numeric data types allow both signed and unsigned integers. They can be
further divided into exact and approximate data types where exact allows the integers in the
form of whole numbers and approximate allow floating integers.
• Character String – This data type allows characters of fixed and variable length. This data type
also can be further categorized into Unicode characters, which allow fixed and variable length of
Unicode characters.
• Binary – The Binary data types allows data to be stored in the format of binary values, for fixed
and variable length.
• Date & Time – This data type allows data to be stored in different formats of date and time.
• Other – This section of data types has data types such as table, XML.
Database operation
create database emp
DROP DATABASE emp
use emp
Table operation
Create table:-
create table employee(
e_id int not null,
e_name varchar(20),
e_salary int,
e_age int,
e_gender varchar(20),
e_dept varchar (20),
primary key(e_id)
Drop table:-
DROP TABLE employee
Alter table:-(add a column)
ALTER TABLE employee
ADD e_email varchar(255)
select * from employee
Alter table:-(drop a column)
ALTER TABLE employee
DROP COLUMN e_email
select * from employee
insert operation—
INSERT INTO
employee(e_id,e_name,e_age,e_salary,e_gender,e_dept)
VALUES (1, 'Ramesh', 32, 2000.00,'male', 'operation' )
select * from employee
INSERT INTO employee(e_id,e_name,e_age,e_salary,e_gender,e_dept)
VALUES (2, 'Annie',34,4000, 'female','analytics'), (3, 'Ace',62,8000, 'male','support'), (4, 'Zelda',
44,5000,'female','sales'),
(5, 'Diesel',34,3000, 'male','operation')
Where—
SELECT * FROM employee
WHERE e_id=1;
Update—
UPDATE employee
SET e_name = 'jemi', e_gender= 'female'
WHERE e_id = 1;
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
Delete—
DELETE FROM employee WHERE e_name='Alfreds Futterkiste';
DELETE FROM table_name;
Truncate—
TRUNCATE TABLE table_name;
Aggregation function—
Performing calculations on multiple rows of a single column of a table
and returning a single value.
select MIN(e_salary) AS SmallestSalary from employee
select MAX(e_salary) AS BiggestSalary from employee
select AVG(e_salary) as avg_sal from employee
select SUM(e_salary) as sal_sum from employee
select COUNT(e_salary) as number from employee where e_salary<6000
CREATE TABLE Student (
id int PRIMARY KEY IDENTITY,
admission_no varchar(45) NOT NULL,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
age int,
city varchar(25) NOT NULL
);
INSERT INTO Student (admission_no, first_name, last_name, age, city)
VALUES (3354,'Luisa', 'Evans', 13, 'Texas'),
(2135, 'Paul', 'Ward', 15, 'Alaska'),
(4321, 'Peter', 'Bennett', 14, 'California'),
(4213,'Carlos', 'Patterson', 17, 'New York'),
(5112, 'Rose', 'Huges', 16, 'Florida'),
(6113, 'Marielia', 'Simmons', 15, 'Arizona'),
(7555,'Antonio', 'Butler', 14, 'New York'),
(8345, 'Diego', 'Cox', 13, 'California');
Logical operators(and,or,not)--
SELECT * FROM student
where id='7' and first_name='Antonio’
SELECT * FROM student
where id='7' or first_name='Diego‘
SELECT * FROM student
WHERE NOT first_name='paul';
Limit--
SELECT * FROM student limit 2;
SELECT * FROM student where city=‘new york’ limit 2;
SELECT * FROM student where city=‘new york’ limit 2,1;
Distinct value:-
SELECT DISTINCT age FROM student;
SELECT count(DISTINCT age) FROM student;
order by:-
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
group by:-
SELECT COUNT(admission_no) as admission, City
FROM student
GROUP BY City
SELECT COUNT(admission_no) as admission, City
FROM student
GROUP BY City
ORDER BY COUNT(admission_no) DESC;
Group by with having:-
SELECT COUNT(admission_no) as admission, City
FROM student
GROUP BY City
HAVING COUNT(admission_no)>=2;
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
AUTO_INCREMENT=100
Sql constraints—
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column if no value is specified
CREATE INDEX - Used to create and retrieve data from the database very quickly
• The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in
a field that appears to be blank.
• A field with a NULL value is a field with no value. It is very important to understand that a NULL
value is different than a zero value or a field that contains spaces.
• NOT NULL signifies that column should always accept an explicit value of the given data type.
There are two columns where we did not use NOT NULL, which means these columns could be
NULL.
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY INT,
PRIMARY KEY (ID)
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS)
VALUES (5, 'Hardik', 27, 'Bhopal');
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS)
VALUES (6, 'Komal', 22, 'MP');
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'tali', 25, 'Mumbai', '');
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, 'Chai', 25, '', 1000 );
SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NULL;
SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;
SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE ADDRESS IS NULL;
SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE ADDRESS IS NOT NULL;
Unique:-
CREATE TABLE Persons (
ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
Foreign key:-
CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
Check and default:-
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age>=18),
City varchar(255) DEFAULT 'Sandnes'
);
INSERT INTO persons (ID, FirstName, LastName, Age, City)
VALUES (3354,'Luisa', 'Evans', 13, 'Texas')
Index—
CREATE INDEX idx_lastname
ON Persons (LastName);
