SIMPLE QUERIES IN SQL

INTRODUCTION TO SQL:

 SQL : STRUCTURED QUERY LANGUAGE

 SQL is a non procedural language which means you only tell WHAT you want, not
HOW to do it.
 SQL Lets you do to the following:
1. Create or change the structure of a Database.
2. Change Security settings.
3. Give users access to database / tables.
4. Ask(Query) for data.
5. Insert, Modify or delete data.
My SQL – SQL Statements:
In this we are going to work with same basic elements
Literals
Datatypes
Nulls
Comments
Literals:
Fixed value used in SQL
Types:
Character Literals
Numeric Literals
CHARACTER LITERALS:
Written in single quotes(MySQL can store upto 4000 bytes)
Ex: ‘Riya’ , ‘Aradhy’, etc
NUMERIC LITERALS:
Written without quotes(MySQL can store upto 53 digits)
Ex : 8, 305, 22
Integer literals: Numbers without decimal places
Ex : 89,90
Real literals : Numbers with decimal places
Ex : 89.56, 90.0
Datatypes:
Define what kind of data can be stored in a field / column.
Categories:
Numeric
Date and Time
String / Text
Numeric:
INT – Normal sized whole number( Signed and Unsigned), Can store up to 11 digits(not
Exactly).
Range : −2³¹ to 2³¹ − 1
TINYINT – Very small numbers, Can store up to 4 digits.
Range : −2⁷ to 2⁷ − 1
SMALLINT – Stores numbers bigger than TINYINT and Smaller than INT, Can store up to 5.
digits.
Range : −2¹⁵ to 2¹⁵ − 1
MEDIUMINT – Can store medium sized numbers, Can store up to 9 digits.
Range : −2²³ to 2²³ − 1
BIGINT – Very large numbers, Can store up to 19 digits.
Range : −2⁶³ to 2⁶³ − 1
FLOAT(M, D):
Stores decimal numbers , Can go up to 24 places.
M = No of Digits
D = Digits after the decimal pointing
Ex: FLOAT(6,2) can be like 1234.89
DOUBLE(M, D) or REAL(M, D):
Stored decimal with more precession than FLOAT, Can go up to 53 places.
DECIMAL(M, D) or NUMERIC(M,D):
Stores exact decimal numbers without rounding.
Date and Time Datatypes:

DATE – Stores date in YYYY-MM-DD format.

Ex : 1973-06-23
DATETIME – Stores Date and Time in YYYY-MM-DD HH:MM:SS format
Ex : 1973-06-23 08:56:45
TIMESTAMP - TIMESTAMP does not store the date directly.
It stores the number of seconds passed since January 1, 1970.
TIME – Stores time in HH:MM:SS format.
Ex : 14:25:30
YEAR(M) – Stores year in 2-digit or 4-digit format (depending on M).
Ex : 2026 / 26
String / Text Types :

CHAR(M) : A fixed length string between 1 and 255 characters(Depending on M

Value).
VARCHAR(M) : A variable length string between 1 and 255 characters.
BLOB or TEXT : A field with maximum length of 65535 characters.
TINYBLOB or TINYTEXT : A field with maximum length of 255 characters.
MEDIUMBLOB or MEDIUMTEXT : A field with maximum length of 16777215
characters.
LONGBLOB or LONGTEXT : A field with maximum length of 429497295 characters.
ENUM : A string object that can have only one value from a predefined list of
allowed values.
Ex : ENUM('Male','Female','Other')
CHAR Vs VARCHAR

Feature CHAR VARCAHR

Type Fixed length Variable length
Padding Adds extra spaces if the No extra spaces
value is shorter
Storage Always takes same space Takes space as per actual
input
Usecase When all entries have same When entries are of
length variable length
NULL Value:

• NULL value means no value at all like a blank cell.

• They are allowed unless restricted by not null or primary key.
• NULL ≠ 0.
• Any operation with NULL gives NULL as a result.
Ex : NULL+10 = NULL
Comments:
These are not executed by SQL, They are just for you to understand the
code.
Types:
Block Comment:
Starts with /* and end with */ , Can span multiple lines.
Inline Comment:
Start with -- followed by space, Ends at line break.
Inline Comment with #:
Starts with # and ends at line break.
SQL Command Syntax:
Commands are the instructions to interact with MySQL Database.
Keywords:
Special words like FROM, WHERE, SELECT in SQL.
Usually Written in Capital letters.
Argument:
Values / Tables/Column name you provide in a clause.
Clauses:
A part of an SQL Command that starts with Keyword and ends with
Argument.

Example:
Symbols in SQL Syntax:

{ }  Grouped as a unit( must be used together)

[ ]  Optional part
…  Can be repeated multiple times
|  or
< >  SQL Terms or Keywords
“ “  Used to enclose identifiers or names, especially when they contain spaces or
special characters.

 Every Command ends with semicolon.

 SQL keywords are not case sensitive – SELECT, select, and SeLeCt work the same.
Text inside quotes is case sensitive – 'Delhi' and 'delhi' are treated as different
values.
Creating and Accessing Databases:

Creating : CREATE DATABASE <database name>;

Using : USE database; (You can use this for already created database also)
To see all the databases : SHOW DATABASES;
PET Table:

COMMANDS:

SELECT : This command is used to get some specified information from the table.
Ex: SELECT name, owner FROM PET;  gives name and owner columns only from
the table.
Ex: SELECT * FROM PET;  Gives entire table.
DISTINCT : Is used to eliminate the redundant data( Duplicate data in a column).
Ex: SELECT DISTINCT species
FROM PET;
 Eliminate the duplicate data of the result data.
To see every thing you can go with ALL
Ex: SELECT ALL species
FROM PET;

 Viewing table structure

DESCRIBE PET;
or
DESC PET;
Simple Calculations Using the keyword SELECT:
Ex: SELECT 1+6;

By default the column name will be your expression, if you want you can change
It by using AS Keyword.
Ex: SELECT 1+6 AS “Sum of 1+6”;
DUAL dummy table:
• Some SQL dialects requires a ‘FROM’ clause.
• MySQL provides a dummy table ‘dual’ for compatibility.
Ex : SELECT 4*3 FROM dual;

In MySQL you can easily omit FROM dual, but DUAL exists for compatibility with
systems like Oracle.
DATE and TIME Functions:
To get Current Date
SELECT CURDATE( );
or
SELECT CURRENT_DATE( );
To get Current Time
SELECT CURTIME( );
or
SELECT CURRENT_TIME( );
To get Current Date and Time
SELECT NOW( );
or
SELECT CURRENT_TIMESTAMP( );
To get Current Timestamp
SELECT UNIX_TIMESTAMP( );  gives time in seconds along with date
 To convert into readable format
SELECT FROM_UNIXTIME( );
To get CURRENT Year:
YEAR( ) : Extracts year from a date / datetime.
SELECT YEAR(CURDATE( ));
or
SELECT YEAR(‘2010-05-13’);
SELECT MONTH(NOW( ));
SELECT DAY(NOW( ));
SELECT HOUR(NOW( ));
SELECT MINUTE(NOW( ));
SELECT SECOND(NOW( ));
SELECT DATE(NOW( ));
SELECT TIME(NOW( ));
Scalar Expressions with selected fields:
You can Combine Columns and you can add arithmetic expressions on it.
Ex : SELECT 22/7 AS PI;

Ex: Let’s take a table Student

Ex1: SELECT name, sex, aggregate/5 AS percentage
FROM Student;
Handling NULL Values:

If you want to substitute NULL value with some other value in the output you can use
IFNULL( ).
Ex: SELECT name, birth, IFNULL( death, ’alive’)
FROM pet;

death  column name

alive  value to substitute
If you want to change the column name you can use

SELECT name, birth, IFNULL( death, ’alive’) AS ‘died on’

FROM pet;
Putting Text in Query Output (SQL):
In SQL, we can add text, symbols, and comments inside the output using SELECT.
This makes the result:
More readable
Easy to understand
Ex1: SELECT name, aggregate, aggregate/5 AS Percentage,"%"
FROM student;
Ex2:

SELECT name, “obtained", aggregate, “ marks and has", aggregate/5,"%"

FROM student;
WHERE Clause in SQL
 Selecting Specific Rows – WHERE Clause
 Used to filter records
 Returns only rows that satisfy a condition
 Written after SELECT statement
Syntax:
SELECT column_name
FROM table_name
WHERE condition;
Display students having aggregate > 350 From Student table.
SELECT name, aggregate
FROM student
WHERE aggregate > 350;
Only records with marks greater than 350 are displayed.
Example 2:
Display name, age and aggregate where age ≥ 16 From student table.
SELECT name, age, aggregate
FROM student
WHERE age >= 16;
Relational Operators
Used to compare values:
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
Example:
SELECT * FROM pet
WHERE city <> ‘cat';
Logical Operators
Used to combine conditions:
AND → Both conditions must be true
OR → At least one condition true
NOT → Reverses condition

Example (OR): || Example (NOT): !

SELECT code, ename SELECT code, ename
FROM employee FROM employee
WHERE grade = 'E2' OR grade = 'E3’; WHERE NOT grade = 'G1';

Example (AND): &&

SELECT code, ename, grade
FROM employee
WHERE grade = 'E4' AND gross < 9000;
OR using ||
SELECT code, ename
FROM employee
WHERE grade = 'E2' || grade = 'E3';

AND using &&

SELECT code, ename, grade
FROM employee
WHERE grade = 'E4' && gross < 9000;

NOT using !
SELECT code, ename
FROM employee
WHERE !(grade = 'G1');
OR + AND
• SELECT code, ename, grade
FROM employee
WHERE (grade = 'E2' OR grade = 'E3')
AND gross < 9000;
Employees who are E2 or E3 AND whose salary is less than 9000.
AND + NOT
• SELECT code, ename, grade
FROM employee
WHERE grade = 'E4'
AND NOT gross < 9000;
Employees who are E4 AND whose salary is NOT less than 9000 (i.e., ≥ 9000).
OR + AND + NOT
• SELECT code, ename, grade
FROM employee
WHERE (grade = 'E2' OR grade = 'E3')
AND NOT grade = 'G1'
AND gross < 9000;
Grade is E2 or E3,AND not G1, AND salary less than 9000.
BETWEEN Operator
• Condition Based on a Range
• Used to select values within a range
Includes both lower and upper limits

Example
SELECT code, ename, gross
FROM employee
WHERE gross BETWEEN 8000 AND 9500;
Displays employees whose gross salary is between 8000 and 9500 (8000 and 9500 are included)

NOT BETWEEN
Example
SELECT code, ename, gross
FROM employee
WHERE gross NOT BETWEEN 8000 AND 9500;
Displays employees whose gross salary is less than 8000 or greater than 9500
• Even though BETWEEN is mostly used for numbers, it also works for text:
SELECT code, ename, grade
FROM employee
WHERE grade BETWEEN 'E2' AND 'E4’;
Displays employees whose grade falls alphabetically between E2 and
E4.
IN and NOT IN Operator
• Condition Based on a List
• IN selects values from a given list
• NOT IN excludes values in the list

Example (IN)
SELECT *
FROM employee
WHERE grade IN ('E2', 'E3', 'E4’);
Displays employees whose grade is either E2, E3, or E4.

Example (NOT IN)

SELECT *
FROM employee
WHERE grade NOT IN ('E2', 'E3’);
Displays employees whose grade is not E2 and not E3.
Conditions Based on Pattern Match (LIKE Operator)

In SQL, pattern matching is used to search for specific patterns in character strings.
It is done using the LIKE operator.
LIKE Operator: The LIKE operator is used in the WHERE clause to search for a specified pattern in a
column.
Syntax:
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;

Wildcard Characters

Wildcard Meaning
% Matches any number of characters (0 or more)
_ Matches exactly one character
Using % (Percent Symbol)
% matches any number of characters.

Example 1: Names starting with 'A'

SELECT ename
FROM employee
WHERE ename LIKE 'A%';
• Starts with A
Can have any characters after A

Example 2: Names ending with 'a'

SELECT ename
FROM employee
WHERE ename LIKE '%a';
• Ends with 'a'
Example 3: Names containing 'vi'
SELECT ename
FROM employee
WHERE ename LIKE '%vi%’;
'vi' can appear anywhere
Using _ (Underscore)
_ matches exactly one character.

Example 1: Second letter is 'a'

SELECT ename
FROM employee
WHERE ename LIKE '_a%';
• First letter → anything
Second letter → must be ‘a’

Example 2: Exactly 4-letter names

SELECT ename
FROM employee
WHERE ename LIKE '____’;
 Exactly 4 characters
Combination of % and _
You can combine wildcards.
Example:
SELECT ename
FROM employee
WHERE ename LIKE 'A__a%';
• Starts with A
Next two characters anything
Fourth character must be 'a'
After that anything
Case Sensitivity
• Pattern matching may be:
• Case-sensitive
• Case-insensitive
In MySQL:
It depends on collation.
Usually case-insensitive by default.
Example:
'A%' matches both Asha and asha (if case-insensitive)
NOT LIKE Operator
Used to select rows that do NOT match a pattern.
Syntax:
SELECT column_name
FROM table_name
WHERE column_name NOT LIKE pattern;
Example:
SELECT ename
FROM employee
WHERE ename NOT LIKE 'A%’;
Displays names not starting with A
ESCAPE Character
Sometimes we need to search for:
%
_
But these are wildcards. So we use ESCAPE.

Example:
SELECT column_name
FROM table_name
WHERE column_name LIKE ‘A\\_%' ESCAPE ‘\\';
Searches for values starting with A_
Here \\ tells SQL to treat _ as normal character
Searching for NULL in SQL
• NULL means no value or missing value.
• It is:
• Not zero (0)
• Not an empty string (' ')
• Not a blank space
• It represents unknown or unavailable data.

Use: IS NULL Suppose some employees do not have a grade. To

SELECT column_name get their data.
FROM table_name SELECT *
WHERE column_name IS NULL; FROM employee
WHERE grade IS NULL;
Searching for NOT NULL Values

Use : IS NOT NULL

SELECT *
FROM employee
WHERE grade IS NOT NULL;
Displays employees who have a grade.
Operator Precedence in SQL
• determines the order in which operators are evaluated in a SQL statement.
• When multiple operators are used in a condition, SQL follows a priority order.
• If we do not use brackets properly, SQL may produce unexpected results.
Order of Operator Precedence (High → Low)
Arithmetic Operators : *, /, %, +, -
Comparison Operators : =, >, <, >=, <=, <>, !=
NOT
AND
OR
• OR has the lowest priority
Arithmetic has highest priority
Example Without Brackets
SELECT *
FROM employee
WHERE grade = ‘E2’ OR grade = ‘E3’ AND gross > 8000;
How SQL evaluates it:
grade = ‘E2’ OR (grade = ‘E3’ AND gross > 8000)
Because:
AND has higher priority than OR.
Example With Brackets (Recommended Way)
SELECT *
FROM employee
WHERE (grade = 'E2' OR grade = 'E3’) AND gross > 8000;
Now SQL will first check:
• Grade E2 or E3
• Then salary condition
• Brackets remove confusion
Always recommended in exams
Sorting Results – ORDER BY Clause
• The ORDER BY clause is used to sort the result of a SELECT query.
• Sorting can be done:
• In Ascending order
• In Descending order

Syntax of ORDER BY
SELECT column_name
FROM table_name
ORDER BY column_name [ASC | DESC];
ASC → Ascending order (default)
DESC → Descending order
Default Sorting (Ascending Order)
If we do not specify ASC or DESC, SQL sorts in ascending order.
Example:
SELECT code, ename, gross
FROM employee
ORDER BY gross;
Sorts salary from lowest to highest.
Sorting in Descending Order
To sort from highest to lowest, use DESC.
Example:
SELECT code, ename, gross
FROM employee
ORDER BY gross DESC;
 Displays highest salary first.
Sorting Based on Text Column
You can sort alphabetically.
Example:
SELECT code, ename
FROM employee
ORDER BY ename ASC;
 Sorts names in alphabetical order.

Sorting Based on Multiple Columns

You can sort by more than one column.
Example:
SELECT code, ename, grade, gross
FROM employee
ORDER BY grade ASC, gross DESC;
First sorts by grade
If grade is same, then sorts by salary (highest first)
ORDER BY with WHERE Clause
ORDER BY is usually written after WHERE.
Example:
SELECT code, ename, gross
FROM employee
WHERE gross > 8000
ORDER BY gross DESC;
Filters first
Then sorts result

ORDER BY with Expressions

You can sort using calculated values.
Example:
SELECT code, ename, gross + 1000 AS revised_salary
FROM employee
ORDER BY revised_salary DESC;
MySQL Functions
• A function is a predefined command.
• It performs an operation and returns a single value.
• Functions operate on zero, one or more values.
• Input values are called arguments or parameters.
Categories of MySQL functions:
 String Functions
 Numeric Functions
 Date and Time Functions
 Aggregate Functions
Common String Functions:
CHAR() – Returns character for given ASCII value
Example: SELECT CHAR(70,65,67,69);
CONCAT() – Joins two or more strings
Example: SELECT CONCAT(name, aggregate);
LOWER() / LCASE() – Converts to lowercase
UPPER() / UCASE() – Converts to uppercase
SUBSTRING() / SUBSTR() – Extracts part of string
TRIM() – Removes leading and trailing spaces
LENGTH() – Returns length of string
Numeric Functions
MOD(a,b) – Returns remainder
Example: SELECT MOD(11,4);
POWER(x,y) – Raises x to power y
Example: SELECT POWER(3,2);
ROUND(n,d) – Rounds number to d decimal places
SIGN(n) – Returns sign of number (-1, 0, 1)
SQRT(n) – Returns square root
TRUNCATE(n,d) – Cuts number to d decimal places
Date & Time Functions in MySQL
CURDATE() – Returns current date
DATE() – Extracts date from datetime
MONTH() – Returns month
YEAR() – Returns year
NOW() – Returns current date & time
SYSDATE() – Returns execution time
Aggregate Functions in MySQL
• Aggregate functions perform calculations on multiple rows of data.
• They return a single result.
• Commonly used with the SELECT statement.
• Often used with GROUP BY clause.
Main Aggregate Functions:
AVG() : Calculates the average value of a numeric column.
COUNT() : Counts the number of rows in a table.
SUM() : Calculates the total sum of a numeric column.
MIN() : Finds the smallest value in a column.
MAX() : Finds the largest value in a column.