SQL Study Material

Download as pdf or txt
Download as pdf or txt
You are on page 1of 20

1. What is SQL?

SQL stands for Structured Query Language. It is developed to manage a database.


SQL enable us to create database, insert data, and update data as per requirement
and retrieve data.
2. What are the Categories of SQL Commands?
All SQL commands are categorized in two types
* Data Definition Language (DDL)
* Data Manipulation language (DML)
3. Data Definition Language (DDL)
* DDL is used to define the structure and constraints of data
* It also defines key constraints, relationships between tables, and other data
validation constraints.
* Examples of DDL commands are:
* CREATE DATABASE - creates a new database
* CREATE TABLE - creates a new table
* ALTER TABLE - modifies a table
* DROP TABLE - deletes a table

4. Data Manipulation Language (DML)

* DML is used to insert, modify, and delete data in a database.


* Examples of DML commands are:
* SELECT — extracts data from a table
* UPDATE - updates data in a table
* DELETE - deletes data from a table
* INSERT INTO — inserts new data into a table

5. What is MySQL?

* MySQL is one of the most popular Relational Database Management System


(RDBMS).
* MySQL was originally founded and developed in Sweden by David Axmark,
Allan Larsson, and Michael Widenius, who had worked together since the
1980s.
6. Characteristics of MySQL

* It is free and Open Source software which does not require to pay for its usage.
* Itis easy to use, quick and reliable.
* It is platform independent software which works on many operating systems like
Windows, UNIX, LINUX etc,
* It is compatible with many programming languages including JAVA, C++, PHP,
PERL, etc.
* It can handle large amount of data very efficiently and accurately.

7. What are the Datatypes used in MySQL?

The given table shows data types commonly used in SQL.

8. Creating a Database

To create a database we will give the CREATE DATABASE command,

Syntax
CREATE DATABASE <database name>;
9. Using a database

To open the database to work USE statements is used.

Syntax
USE <databasename>;

10. Viewing the current database

To view the name of current database we can use SELECT DATABASE();


command.

Syntax

SELECT DATABASE();

11. Drop Database

To delete a database we can use DROP DATABASE command.


Syntax:
DROP DATABASE <database name>;

12. Show tables

To display a list of tables stored in a database we can use SHOW TABLES


command.

Syntax:
SHOW TABLES;
13. Create Table command

This command is used to create a new table or relation.


Syntax:
CREATE TABLE<table name>
(
<column 1><data type> [constraint] ,
<column 2> <data type> [constraint],
<column 3> «data type> [constraint]
);
Note: [Constraint] is optional

Example:

CREATE TABLE Item


(
Itemno char(5) primary key,
Itemname varchar(20),
Rate float NOT NULL,
Qty int
);

14. What are types of Database Constraints?

Database enforces 5 types of constraints mentioned here under:


Not Null
Sometimes an attribute may not be allowed to be left blank. Hence Not Null
constraints can be associated in this case.
Example:
CREATE TABLE TEACHER
(
Teacher_ID INTEGER,
First name varchar(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Date_of Birth DATE,
Salary FLOAT
);
Default

To make a value by default for an attribute Default constraint can be used.


CREATE TABLE TEACHER
(
Teacher_ID INTEGER,
First_ NameVARCHAR(20) NOT NULL,
Last_NameVARCHAR(20),
Gender CHAR(1),
Date_of Birth DATE,
Salary FLOAT DEFAULT 20000
);

Primary Key
It ensures following when applied:
* Duplicate data is not allowed in the attribute.
* Attribute cannot be left blank
* A relation can have only a primary key
Example:
Foreign Key

It ensures a relationship between relations. The main purpose of this constraint


is to check that data entered in one relation is consistent with the data entered in
another relation.

Example:
Consider the following tables:
Item (Itemno, Iname, rate)
Orders (Ono, Odate, Itemno, qty, total)
Here in this example Itemno of order table is the foreign key that references
Itemno of Item table which is Primary key.

Create table Orders


(
Ono int Primary Key,
Odate date,
Itemno int,
qty int,
total int,
FOREIGN KEY (Itemno) REFERENCES Item (Itemno)
);

Unique Key
It ensures following when applied:
* Duplicate data is not allowed in the attribute.
* A relation can have multiple unique keys
Example:

CREATE TABLE EMPLOYEE


(
emp_id integer PRIMARY KEY,
emp_namevarchar(20) NOT NULL,
gender char(1),
date_of birth date,
salary float,
dept varchar(20) UNIQUE
);
Viewing Structure of table | DESCRIBE command

* We can use DESCRIBE or DESC command to view the structure of a table as


mentioned in CREATE TABLE command.
* It displays table structure including attributes, data types, and constraints
Syntax:
DESCRIBE <table name>;
Or
DESC <table name>;
Example

Drop table

This command is used to delete a table completely from database. It deletes


structure and data both of table from database.

Syntax:
DROP TABLE <table name>;
Example
DROP TABLE Orders;
15. How to modify the structure of the table?

Alter table
* It is used to modify the structure of a table. Following are the alterations that
can be done using this command adding a column
* We can add a new column in an existing table.

Example:

ALTER TABLE school ADD phone char (10) NOT NULL;

Dropping a column

A column can be dropped using alter table command as given below:


Example
ALTER TABLE Item DROP purchase_date;
Modifying a table:

A column can also be modified using Alter Table command as given below:

ALTER TABLE item MODIFY rate int DEFAULT 100;

16. How to add Primary Key?

ALTER TABLE item ADD PRIMARY KEY ino;


17. How to drop primary key?

ALTER TABLE Item DROP Primary key;

DML
Insert command

This command is used to insert a tuple in a relation. We must specify the name of the
relation in which tuple is to be inserted and the values. The values must be in the same
order as specified during the Create Table command.

Syntax:

INSERT INTO <tablename> VALUES (<values >);

Example: Inserting values in table Item (Ino, Iname, Rate, Qty)

INSERT INTO Item Values (1, ‘soap’,44,120);


Update command
Update command is used to modify the attribute values of one or more tuples in
a table.
Syntax:

UPDATE <tablename> SET <expression> WHERE <criteria>;

Example:

UPDATE Item SET Rate = 30 WHERE itemno = ‘0001’;

Delete command

To delete one or more tuples (rows) in a relation DELETE command is used


Syntax:
DELETE FROM <tablename> WHERE <criteria>;

Example:
DELETE FROM item WHERE itemno = 10002’;
Select command

The select command is used to access or retrieve specific or complete set of records
from the database.
Syntax:
SELECT <attribute list> FROM <table name>;

Query1: Display all records from Item table.

Query2: Display itemname with its rate from item table.

Query3: display name of items which rate is more than 100.


Query4: display item records which name begins with s.

Query 5 : Retrieve item details which rate is less than 100 and qty is more than
100.

DISTINCT Command

DISTINCT command is used to eliminate repeated data of a column when display


using SELECT command

Syntax:
SELECT DISTINCT (field that contains repeated data) FROM <table name>
WHERE <criteria>;
Example
SELECT DISTINCT(dept) FROM emplovee:

WHERE clause

© WHERE clause act as a Filter while manipulate records in SQL.


© Where clause is used to define Criteria for the records to be retrieved.
© It can be used with any DML command such as SELECT, DELETE and
UPDATE.
© To define criteria various operators like Arithmetic, Range or Comparison
operators are used in WHERE clause.
Syntax:
SELECT <field(s)> FROM <table name> WHERE <criteria>;

Operators used with WHERE clause

* Arithmetic operators
* Relation operators
* Logical operators
* BETWEEN operators
* IN operators
* LIKE operators

Arithmetic operators

* We can use arithmetic operators to define expressions in DML SQL commands.


* Arithmetic operators are used with numeric values.
* Following table depicts various arithmetic operators:
Relational operators

* We can use Relational operators to compare values in SQL.


* Mostly used to define criteria in WHERE clause to filter records.
Following table depicts various Relational operators:

Logical operators

* Logical operators are used to combine multiple criteria/expression in DML


SQL commands
* Mostly used to combine criteria in WHERE clause to filter records
* Following table depicts various Logical operators:

BETWEEN operator
© Itis used to define range of values for a particular field in WHERE clause.
© It can be used to define range of numerical or chronological values
© In upper and lower bound given in the range.
IN operator
© It is used to set multiple text comparison for a particular field in WHERE
clause,
© It can only be used for text comparison.
LIKE operator
* It is used to define string expressions (pattern matching) in WHERE clause.
* It uses different wildcards (described in image given below) to define string
expression

Order by clause
* Used to display records in sequential manner in data result set retrieved by
WHERE clause.
* By default displays records in ascending order.
* Keyword ASC (Ascending) or DESC (descending) can be used with order by to
arrange the data result set.

Example
SELECT * FROM item ORDER BY iname;
SELECT * FROM item OREDER BY iname DESC;

What is NULL Value?


* NULL represents a missing or unknown value in SQL.
* NULL is different from 0 (zero).
* Any arithmetic operation performed with NULL value give NULL.
* To check for NULL value in a column we use IS NULL operator.

Example

Aggregate function

* Are also known as Multiple Row Functions.


* It works on Multiple rows together rather single row
* It helps to summarize large volume of data
* It returns single value for entire table or set of rows
* Only one column can be specified within parenthesis of aggregate function
* Multiple aggregate functions can be used together in a SQL Query by
separating them using commas.
Group By

The Group By clause helps to summarize large volume of records. It combines all
those records that have

Identical values in a particular field or a group of fields and produces one summary
record per group.

We can use aggregate functions to work on the grouped values.


Syntax:
SELECT <field(s)>, <aggregate function(s)>
FROM <table name>
WHERE <condition>
GROUP By <field(s)>
Example

Having clause
* Helps to filter summarized result produced after grouping.
* It is like post filter which retrieves records from result set produced after
group by query.
* It can include aggregate functions to set the filter criteria

Syntax:
SELECT <field(s)>, <aggregate function(s)>
FROM <table name>
WHERE <condition>
GROUP By <field(s)>
HAVING <expression>
ORDER BY <field(s)> ASC/DESC
Joins

A Join ia an operation which is used to combine tuples from one or more


tables based on one or more common field between them.
In another way we can say that it is query written to retrieves data from
two or more tables as per given condition.

Different types of joins are:


*Cartesian product
* Equi join
* Inner Join
* Outer join(Left / Right Join)
* Natural join

SQL INNER JOIN

The SQL INNER JOIN command joins two tables based on a common column and
selects rows with matching values in those columns.

-- join Customers and Orders tables


-- select customer_id and first_name columns from the Customers table
-- select amount from the Orders table

SELECT Customers.customer_id, Customers.first_name, Orders.amount


FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer;

Here, the Customers and Orders table are joined on the Customers.customer_id =
Orders.customer condition. The above code excludes all the rows that don't satisfy
this condition.

SQL INNER JOIN Syntax


The syntax of the SQL INNER JOIN statement is:

SELECT columns_from_both_tables
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2

Here,
 table1 and table2 are the two tables that are to be joined
 column1 is a column in table1 and column2 in a column in table2
Note: We can use JOIN instead of INNER JOIN. Basically, these two clauses
perform the same task.
SQL INNER JOIN

-- join the Customers and Orders tables when


-- the customer_id from Customers matches the customer column in Orders

SELECT Customers.customer_id, Customers.first_name, Orders.amount


FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer;

SQL LEFT JOIN Syntax


The syntax of the SQL LEFT JOIN statement is:

SELECT columns_from_both_tables
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column2
Here,
 table1 is the left table to be joined
 table2 is the right table to be joined
 column1 and column2 are the related columns in the two tables

SQL LEFT JOIN


The SQL LEFT JOIN joins two tables based on a common column. It selects
records that have matching values in these columns and the remaining rows from the
left table.
-- left join Customers and Orders tables based on their shared customer_id columns
-- Customers is the left table
-- Orders is the right table

SELECT Customers.customer_id, Customers.first_name, Orders.item


FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id;

Here, the code left joins the Customers and Orders tables based on customer_id,
which is common to both tables. The result set contains:
 customer_id and first_name columns from the Customers table (including
those whose customer_id value is not present in the Orders table)
 item column from the Orders table

You might also like