SQL_all_commnads_aggregate_functions.pptx

SQL
(Structured Query Language)

Topics Covered :
➢ Database and its types
➢ Introduction to SQL
➢ Syntax
➢ Data Types
➢ SQL Commands
➢ Operators
➢ Aggregate Functions

Database and its types :
What is data? (BOOKS)
➔ Data is a collection of a distinct unit of
information. This “data” is used in a variety of
forms of text, numbers, media and many more.
What is database? (LIBRARY)
➔ The database is an organized collection of
structured data to make it easily accessible,
manageable and update. In simple words, you
can say, a database in a place where the data is
stored.
Types of Databases :
1. Distributed Database
2. Object-Oriented Database
3. Centralized Database
4. Operational Database
5. Graph Database
6. NoSQL Database
7. Relational Database

Introduction to SQL :
★ SQL (Structured Query Language) is used to perform operations on the records
stored in the database, such as updating records, inserting records, deleting records,
creating and modifying database tables, views, etc.
★ SQL is not a database system, but it is a query language.
★ SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in
the early 1970s.
★ Uses very simple English language.
★ Initially called as SEQUEL (Structured English Query Language).

SQL Syntax :
A simple example to show syntax:
CREATE DATABASE NAME;
USE NAME;
CREATE TABLE TABLE_NAME;
SELECT COLUMN FROM TABLE;

SQL Data Types :
Data types mainly classified into three categories for every database.
● String Data types
● Numeric Data types
● Date and time Data types
String Data types :
➢ CHAR(Size) : It is used to specify a fixed length string that can contain numbers, letters, and special characters. Its
size can be 0 to 255 characters. Default is 1.
➢ VARCHAR(Size) : It is used to specify a variable length string that can contain numbers, letters, and special
characters. Its size can be from 0 to 65535 characters.

SQL Data Types :
➢ BINARY(Size) : It is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column
length in the bytes. Default is 1.
➢ TEXT(Size) : It holds a string that can contain a maximum length of 255 characters.
Numeric Data types :
➢ BIT(Size) : It is used for a bit-value type. The number of bits per value is specified in size. Its size can be 1 to 64.
The default value is 1.
➢ INT(size) : It is used for the integer value. Its signed range varies from -2147483648 to 2147483647 and unsigned
range varies from 0 to 4294967295.
➢ DECIMAL(size, d) : It is used to specify a fixed point number. Its size parameter specifies the total number of
digits. The number of digits after the decimal parameter is specified by d parameter.

SQL Data Types :
Date and time Data types :
➢ DATE : It is used to specify date format YYYY-MM-DD.
➢ DATETIME(fsp) : It is used to specify date and time combination. Its format is YYYY-MM-DD
hh:mm:ss.
➢ TIME(fsp) : It is used to specify the time format. Its format is hh:mm:ss.
➢ TIMESTAMP(fsp) : It is used to specify the timestamp. Its value is stored as the number of seconds.
➢ YEAR : It is used to specify a year in four-digit format.

SQL Commands :
DDL (Data Definition Language): used on tables
1. CREATE
2. DROP
3. ALTER
4. TRUNCATE
5. RENAME
DML(Data Manipulation Language): used on records
1. SELECT
2. INSERT
3. UPDATE
4. DELETE

DDL Commands : (used on the table)
CREATE :
CREATE TABLE
table_name (
column1
datatype,
column2
datatype,
column3
datatype,
....
);
DROP : (Delete the table)
DROP TABLE table_name;
ALTER :
1. ALTER TABLE
table_name
ADD column_name
datatype;
TRUNCATE : (Delete data from the
table)
TRUNCATE TABLE table_name;
RENAME :
RENAME TABLE table_name TO
table_name_new ;

DML Commands : (used on the data)
SELECT :
1. SELECT column_Name_1, column_Name_2, ….., column_Name_N FROM Name_of_table;
INSERT :
1. INSERT INTO TABLE_NAME ( column_Name1 , column_Name2 , column_Name3 , .... column_NameN )
VALUES (value_1, value_2, value_3, .... value_N ) ;
UPDATE :
1. UPDATE Table_name SET [column_name1= value_1, ….., column_nameN = value_N] WHERE CONDITION;

DML Commands : (used on the data)
DELETE :
1. DELETE FROM Table_Name WHERE condition;
EXAMPLES :
➢ SELECT : https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_columns
➢ INSERT : https://www.w3schools.com/sql/trysql.asp?filename=trysql_insert_colname
➢ UPDATE : https://www.w3schools.com/sql/sql_update.asp
➢ DELETE : https://www.w3schools.com/sql/sql_delete.asp

DELETE vs TRUNCATE vs DROP
➔ DROP and TRUNCATE are DDL commands where as DELETE is DML command.
➔ Syntax for both DROP and TRUNCATE are same.
➔ DROP : This command removes the complete table from the database.
➔ DELETE : This command remove either a particular record or all the records present
in the table.
➔ TRUNCATE : This command removes all the records present in the table.

SQL OPERATORS :
ARITHMETIC OPERATORS :

SQL OPERATORS :
COMPARISION OPERATORS :

SQL OPERATORS :
LOGICAL OPERATORS :
1. AND: The SQL AND operator is used with the where clause in the SQL Query. AND operator in
SQL returns only those records which satisfy both the conditions in the SQL query.
2. OR : The SQL OR operator is used with the where clause in an SQL Query. AND operator in SQL
returns only those records that satisfy any of the conditions in the SQL query.
3. NOT : NOT operator in SQL shows those records from the table where the criteria is not met. NOT
operator is used with where clause in a SELECT query.
4. BETWEEN : This operator displays the records which fall between the given ranges in the SQL
query. The results of the BETWEEN operator include begin and end values of the given range.
5. IN : When we want to check for one or more than one value in a single SQL query, we use IN
operator with the WHERE clause in a SELECT query.
6. LIKE : LIKE Operator in SQL displays only those data from the table which matches the pattern
specified in the query. Percentage (%) and underscore (_) are the two wildcard operators used with
LIKE Operator to perform pattern matching tasks.

SQL OPERATORS :
LOGICAL OPERATORS :
★ All the logical operators are used in WHERE, HAVING clause i.e., in checking condition.
★ WILDCARDS :
Percentage (%) and underscore (_) are the two wildcard operators used with LIKE operator to perform pattern matching
tasks.
( % ) – represents zero or more characters.
( _ ) – represents a single character.
Ex : Ram → R%
Ram → R_m

AGGREGATE FUNCTIONS :
1. COUNT
The COUNT() function returns the number of rows that matches a specified criterion.
2. SUM
The SUM() function returns the total sum of a numeric column.
3. AVG
The AVG() function returns the average value of a numeric column.
4. MAX
The MAX() function returns the largest value of the selected column.
5. MIN
The MIN() function returns the smallest value of the selected column.

SELECT COUNT(*) FROM CUSTOMERS;
>>> 5
—----------------------------------------------------------
SELECT SUM(AGE) AS T_AGE
FROM CUSTOMERS;
>>> 128
—----------------------------------------------------------
SELECT AVG(AGE) AS A_AGE
FROM CUSTOMERS;
>>> 25.6

SELECT MAX(AGE)
FROM CUSTOMERS;
>>> 31
—----------------------------------------------------------
SELECT MIN(AGE)
FROM CUSTOMERS;
>>> 22

SQL_all_commnads_aggregate_functions.pptx

More Related Content

Similar to SQL_all_commnads_aggregate_functions.pptx

Recently uploaded

SQL_all_commnads_aggregate_functions.pptx