https://github.com/t-sibiraj/sql Light Mode Dark Mode Download More

TABLE OF CONTENTS:

xxxxxxxxxx

New topics will be added from time to time.

xxxxxxxxxx

xxxxxxxxxx
Visit this link ( https://github.com/t-sibiraj/sql ) to get the latest version of this pdf.

DATABASE CONCEPTS:

# SKIP TO SQL IF YOU ALREADY KNOW DATABASE CONCEPTS #

CLICK ME TO SKIP

EXTENDED READING FOR DATABASE CONCEPTS:

https://cbseacademic.nic.in/web_material/doc/cs/2_Computer_Science_Python_ClassXII.pdf

INTORDUCTION:

Let us consider school1 has a database. In which classes class 11 and class 12 are tables. In each class(tables) there are two columns(boys and girls). Each column has two rows(B has B1 , B2 as well as G has G1, G2)

SOURCE: t-sibiraj.github.io/learn

SOURCE: t-sibiraj.github.io/learn

xxxxxxxxxx
Database: School1 and School2  (Collection of tables and databases)
Tables: Class 12 and Class 11  (Collectiion of rows and columns)

In Class 12(Table)

There are two columns B abd G. There are two records in B(B1 , B2) 
There are two rows


We can have a database named *city* which could have the databases *school1* and *school2* in it.

DATABASE

xxxxxxxxxx
Database is a collection of related information that is organized in such a way that it supports for easy access, modification and maintenance of data

Examples of database: Ms-Access, MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Oracle, SAP, dBASE, FoxPro, etc..

RELATION aka Table:

xxxxxxxxxx
Relation is nothing but a table which is made up of rows and columns

DOMAIN:

A domain is a set of acceptable values of a particular column, which is based on various properties and data types.

For example:

(i) The domain of gender column has a set of two possible values i.e, Male or Female.

(ii) The domain of marital status has a set of four possible values i.e, Married, Unmarried, Widows and Divorced

** (iii) The domain of subject has a set of five possible values i.e., Math's,physics,chemistry,computer science and English

TUPLE aka ROW:

xxxxxxxxxx
Horizontal subset/information in a table is called tuple. 
The tuple is also known as a 'record', which gives particular information of the relation (table). 

For example: 

(i) In customer table, one row gives information about one customer only. 

(ii) In student table, one row gives information about one student only.

ATTRIBUTE AKA Column:

xxxxxxxxxx
Attribute is also known as Columns or column

Degree:

xxxxxxxxxx
The number of attributes(fields)(column) in a table

Degree ---> no of columns

Cardinality:

xxxxxxxxxx
The number of tuple(record)(rows) in a table

Cardinality ---> no of rows

KEY:

Key is of four types:

(i) Primary Key

(ii) Candidate Key

(iii) Foreign Key

(iv) Alternate Key

Primary Key:

xxxxxxxxxx
A column or **set of columns that uniquely identifies a row** within a table is called primary key. 

PRIMARY KEY ---> THIS IS SERVES AS AN UNIQUE INDENTIFIER
           ---> TWO PERSON CAN HAVE SAME NAME BUT THEY CAN''T HAVE SAME FINGERPRINT
            ---> HERE FINGERPRINT SERVES THE PURPOSE OF PRIMARY KEY
           ---> IN TABLE WE MUST HAVE A PRIMARY KEY TO UNIQUELY IDENTIFY A RECORDS IN A TABLE 

                   ╔════╦══════╦════════╗              
                   ║ id ║ name ║ gender ║
                   ╠════╬══════╬════════╣
                   ║ 2  ║ sam  ║ male   ║
                   ╠════╬══════╬════════╣
                   ║ 1  ║ ram  ║ female ║
                   ╠════╬══════╬════════╣
                   ║ 3  ║ ram  ║ male   ║
                   ╚════╩══════╩════════╝
                  TABLE NAME: GENDER 

IN THE TABLE GENDER WE CAN SELECT id HAS PRIMARY KEY AS IT ONLY HAS UNIQUE RECORDS. WE CAN''T USE NAME AND GENDER AS PRIMARY KEY AS TWO PERSON CAN HAVE SAME NAME AND TWO PERSON CAN HAVE SAME GENDER

Candidate Key:

xxxxxxxxxx
Candidate keys are set of fields (columns with unique values) in the relation that are eligible to act as a primary key.

Candidate key  = Collection of Primary key

Alternate Key:

xxxxxxxxxx
Out of the candidate keys, after selecting a key as primary key, the remaining keys are called alternate key.

Alternate Key =  Candidate key - Primary key

Foreign Key:

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In other words, a foreign key is a column or a combination of columns that is used to establish a link between two tables.

xxxxxxxxxx
#FOREIGN KEY CONSTARINT

#FOREIGN KEY IS LIKE PRIMARY KEY. IT IS USED IN RDBMS.

#SO FAR WE HAVE ONLY SEE DBMS.

DBMS ---> Database Management System
RDBMS ---> Relational Database Management System

IN RDBMS TABLES ARE IN RELATION WITH EACH OTHER BUT IN DBMS TABLES ARE NOT IN  RELATION WITH EACH OTHER.


DBMS

DATABSSE NAME: RDBMS

╔════╦══════╦══════╗                   ╔════╦══════╦════════╗              
║ id ║ name ║ age  ║                   ║ id ║ name ║ gender ║
╠════╬══════╬══════╣                   ╠════╬══════╬════════╣
║ 1  ║ ram  ║ 10   ║                   ║ 2  ║ sam  ║ male   ║
╠════╬══════╬══════╣                   ╠════╬══════╬════════╣
║ 2  ║ sam  ║ 20   ║                   ║ 1  ║ ram  ║ female ║
╠════╬══════╬══════╣                   ╠════╬══════╬════════╣
║ 3  ║ ram  ║ 30   ║                   ║ 3  ║ ram  ║ male   ║
╚════╩══════╩══════╝                   ╚════╩══════╩════════╝
TABLE NAME: AGE                        TABLE NAME: GENDER 


AS YOU CAN SEE BOTH THE TABELS ARE RELATED TO EACH OTHER BY THE ID COLOUMN

id column in table AGE is called the primary key and id in table GENDER is called primary key

id column is called as the foreign key as it is used to relate the two tables AGE AND GENDER.
WE can even choose the gender column as the primary key but we can''t choose it as foreign key as it is not present in the age table.


DATABASE DBMS:
╔═══════╦══════╦══════╗                   ╔════╦══════╦════════╗              
║ weight║ name ║ age  ║                   ║ id ║ name ║ gender ║
╠═══════╬══════╬══════╣                   ╠════╬══════╬════════╣
║ 40    ║ ram  ║ 10   ║                   ║ 2  ║ sam  ║ male   ║
╠═══════╬══════╬══════╣                   ╠════╬══════╬════════╣
║ 50    ║ sam  ║ male ║                   ║ 1  ║ ram  ║ female ║
╠═══════╬══════╬══════╣                   ╠════╬══════╬════════╣
║ 60    ║ ram  ║ male ║                   ║ 3  ║ ram  ║ male   ║
╚═══════╩══════╩══════╝                   ╚════╩══════╩════════╝      
TABLE NAME: AGE                            TABLE NAME: GENDER 

TWO TABLES ARE NOT RELATED TO EACH OTHER SO IT IS CALLED AS DBMS

SOURCE: t-sibiraj.github.io/learn

xxxxxxxxxx
degree       ---> 4(in first table)
cardianality ---> 3(in first table)

Resources:

xxxxxxxxxx
https://powerbidocs.com/2019/12/25/sql-keys/

xxxxxxxxxx
SUMMARY:

row    <->  cardinality <-> tuple  <->  record
column <->  degree      <-> field  <->  attribute

Primary key    =  Can use used to uniquely identify the record
Candidate key  =  Collection of Primary key
Alternate Key  =  Candidate key - Primary key

Advantages of SQL:

xxxxxxxxxx
(i) SQL is portable

(ii) High Speed

(iii) Easy to learn

(iv)SQL is used with any DBMS system with any vendor: SSQL is used for relational databases: SQL is widely used for relational databases. 

(v)SQL acts as both programming language and interactive language: 

(vi)Client/Server language:

(vii)Supports object based programming

SOME MYSQL SQL ELEMENTS:

(i) Literals(have fixed value):

xxxxxxxxxx
numeric literal --> 53 ,64

string literal ---> "abc"

real literals ---> 17.0 ,  17.5

(ii) Data Types:

(I) Numeric :

(I) Number:

xxxxxxxxxx
Number:

Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits.

Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits.
Zero.

xxxxxxxxxx
NUMBER(x,y)  ---> x -> total numbe of digits
                  y -> digits after decimal point

NUMBER(x) or NUMBER(x,0)  ---> Acts like integer

#NOT SUPPORTED IN MYSQL 8.0 --> NUMBER(X) , NUMBER(X,Y) , NUMBER

xxxxxxxxxx
INTEGER(x)  ---> x here presents the number the total number of digits

INTEGER  ---> whole numbers between -2,147,483,648 and 2,147,483,647.

xxxxxxxxxx
SMALLINT --> 5 DIGIT INTEGER
INTEGER  --> 10 DIGIT INTEGER
BIGINT   --> 19 DIGIT INTEGER

(II) Decimal/Float:

xxxxxxxxxx
DECIMAL[ (p [, s])] 

– 'p' the total number of significant decimal digits
– 's' the number of digits from the decimal point to the least significant digit.

NOTE: IF YOU PASS AN INTEGER VALUE TO DECIMAL(X) OR DECIMAL(X,0), IT WILL BE STORED AS INTEGER. IF YOU PASS THE SAME INTEGER TO DECIMAL(X,Y) THEN THERE WILL BE Y ZEROES AFTER THE DECIMAL POINT.

12345  --> VALUE TO BE INSERTED

CREATE TABLE D1(id DECIMAL(5));        ---> 12345
CREATE TABLE D1(id DECIMAL(5,0));      ---> 12345
CREATE TABLE D1(id DECIMAL(5,3));      ----> 12345.000

FLOAT , FLOAT(X,Y) , FLOAT(X)  --> SIMILAR TO DECIMAL() , DECIMAL(X,Y) , DECIMAL(X)

(III) INT/INTERGER

(IV) FLOAT

(ii) CHARACTER OR STRING:

xxxxxxxxxx
CHAR(10) has fixed length, right padded with spaces.
VARCHAR(10) has fixed length, right padded with NULL
VARCHAR2(10) has variable length.

the difference between VARCHAR and VARCHAR2 is that VARCHAR is an ANSI standard and it takes up space for variables, whereas the VARCHAR2 is used only in Oracle but makes more efficient use of space.

#NOT SUPPORTED IN MYSQL 8.0 --> VARCHAR2

(III) DATE AND TIME:

xxxxxxxxxx
DATE: 'YYYY-MM-DD' -> '2021-01-01'

DON''T MISS THE QUOTES

xxxxxxxxxx
DATETIME: 'YYYY-MM-DD HH:MM:SS' ---> '2021-01-01 10:10:10'

DON''T MISS THE QUOTES

xxxxxxxxxx
TIME: HH:MM:SS ---> '11:59:10'

DON''T MISS THE QUOTES

xxxxxxxxxx
YEAR: 

-> YEAR    -> 4-digit format
-> YEAR(2) -> 2-digit format(21)   #NOT SUPPORTED IN MYSQL 8.0
-> YEAR(4) -> 4-digit format(2021) #NOT SUPPORTED IN MYSQL 8.0

xxxxxxxxxx
TIMESTAMP: (YYYYMMDDHHMMSS) --> 20210101060510

NO NEED TO USE QUOTES.KEY IN(INPUT) AS NUMBER(INTEGER).

TYPES OF SQL STATEMENTS

(i)Data Definition Language (DDL) statement:

DDL statements are used to create structure of a table, modify the existing structure of the table and remove the existing table. Some of the DDL statements are CREATE TABLE, ALTER TABLE and DROP TABLE.

Grant and revoke privileges and roles and maintenance commands

(ii) Data Manipulation Language (DML) statement:

Data Manipulation Language (DML) statements are used to access and manipulate data in existing tables. The manipulation includes inserting data into tables, deleting data from the tables, retrieving data and modifying the existing data. The common DML statements are SELECT, UPDATE, DELETE and INSERT.

(iii) Transaction Control Language (TCL) Commands:

COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION

(iv) Session Control Commands

(v) System Control Commands

SQL

(Structured Query Language is a standard language used for accessing databases)

(ALL THE SQL COMMANDS WHICH ARE LISTED BELOW ARE COMPITABLE WITH MySQL

MySQL :https://dev.mysql.com/doc/

SQL Server:https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15)

• SQL IS CASE INSENSITIVE

  ---

Consider

Name of the table --> records

TABLE CREATION COMMANDS:

xxxxxxxxxx
DATATYPES:

char() varchar() integer() decimal() '2001-12-30'
char   varchar   integer   decimal NUMBER()

xxxxxxxxxx
#TO CREATE A DATABASE

SYNTAX:  CREATE DATABASE database_name;
EXAMPLE: CREATE db;

# IF WE DON'T KNOW WHEATHER A DATABSE EXISTS OR NOT --> WE CAN USE  ---> IF NOT EXISTS

CODE: CREATE DATABASE IF NOT EXISTS db;
/* db database will be created if it not exists */

xxxxxxxxxx
#TO CREATE A TABLE

SYNTAX:  CREATE TABLE table_name 
        (column_name property, ....);

EXAMPLE: CREATE TABLE records 
      (sno integer, student_name varchar(10) , email varchar(20), year integer,column_name integer) ;

**NOTE: WE MUST USE A DATABASE BEFORE WE CREATE TABLES AND A TABLE MUST HAVE AT LEAST ONE VISIBLE COLUMN**

xxxxxxxxxx
#TO ADD A SINGLE RECORD

SYNTAX: INSERT INTO table_name VALUES  (item_name , .....)
NOTE: WE CAN ALSO USE VALUE INTSEAD OF VALUES AS WE ADD A SINGLE RECORDS

EXAMPLE: INSERT INTO records VALUES
        (1, 'ram', 'ram@gmail.com', 2004, 10)

xxxxxxxxxx
#TO ADD MULTIPLE REOCRDS

SYNTAX: INSERT INTO table_name VALUES
        (row_1_item_no_1 , row_1_item_no_2 , row_1_item_no_3)  
        (row_2_item_no_1 , row_2_item_no_2 , row_2_item_no_2)
                        ....
                        ....
        (row_n_item_no_1 , row_n_item_no_2 , row_n_item_no_3)               

CODE: INSERT INTO records VALUES
      (2, 'sam', 'sam@yahoo.com', 2003, 20),
      (3, 'hari', 'hari@outlook.com', 2002, 30),
      (4, 'ramu', 'ramu@gmail.com', 2004, 20);


ANOTHER WAY:

SYNTAX: INSERT INTO table_name(columns) VALUES
        (row_1_item_no_1 , row_1_item_no_2 , row_1_item_no_3)  
        (row_2_item_no_1 , row_2_item_no_2 , row_2_item_no_2)
                        ....
                        ....
        (row_n_item_no_1 , row_n_item_no_2 , row_n_item_no_3)

CODE: INSERT INTO records(sno,student_name,email,year,column_name) VALUES
      (2, 'sam', 'sam@yahoo.com', 2003, 20),
      (3, 'hari', 'hari@outlook.com', 2002, 30),
      (4, 'ramu', 'ramu@gmail.com', 2004, 20);
        

SELECT

xxxxxxxxxx
#TO SELECT A SINGLE COLUMN

SYNTAX: SELECT column_name
        FROM table_name;

EXAMPLE: SELECT student_name
        FROM records;
OUTPUT:
        +--------------+
        | student_name |
        +--------------+
        | ram          |
        | sam          |
        | hari         |
        | ramu         |
        +--------------+

xxxxxxxxxx
#TO SELECT MULTIPLE COLUMN

SYNTAX: SELECT column_name_1,column_name_2,column_name_3
        FROM table_name;

EXAMPLE: SELECT student_name , year;
        FROM records;

OUTPUT: 
        +--------------+------+
        | student_name | year |
        +--------------+------+
        | ram          | 2004 |
        | sam          | 2003 |
        | hari         | 2002 |
        | ramu         | 2004 |
        +--------------+------+

xxxxxxxxxx
#TO SELECT ALL THE COLUMNS FROM A TABLE:

SYNTAX: SELECT *
        FROM table_name;

EXAMPLE: SELECT * 
        FROM records;

OUTPUT:
        +------+--------------+------------------+------+-------------+
        | sno  | student_name | email            | year | column_name |
        +------+--------------+------------------+------+-------------+
        |    1 | ram          | ram@gmail.com    | 2004 |          10 |
        |    2 | sam          | sam@yahoo.com    | 2003 |          20 |
        |    3 | hari         | hari@outlook.com | 2002 |          30 |
        |    4 | ramu         | ramu@gmail.com   | 2004 |          20 |
        +------+--------------+------------------+------+-------------+ 

xxxxxxxxxx
#TO SELECT ALL THE VALUES FROM A COLUMN WITHOUT ANY DUPLICATES RECORDS

SYNTAX: SELECT DISTINCT column_name
        FROM table_name;

EXAMPLE: SELECT DISTINCT year
        FROM records;

OUTPUT:
        +------+
        | year |
        +------+
        | 2004 |
        | 2003 |
        | 2002 |
        +------+

xxxxxxxxxx
#USING ARITHMETIC OPERATORS

+ , - , * , / , %

#SELF - EXPLANATORY
CODE: SELECT year , year * 100 
      FROM records;

OUTPUT:
        +------+------------+
        | year | year * 100 |
        +------+------------+
        | 2004 |     200400 |
        | 2003 |     200300 |
        | 2002 |     200200 |
        | 2004 |     200400 |
        +------+------------+
===================================================================================================

CODE: SELECT year ,  year * 10 , "TEST"
      FROM records;

OUTPUT:
        +------+-----------+------+
        | year | year * 10 | TEST |
        +------+-----------+------+
        | 2004 |     20040 | TEST |
        | 2003 |     20030 | TEST |
        | 2002 |     20020 | TEST |
        | 2004 |     20040 | TEST |
        +------+-----------+------+
===================================================================================================

#SELF-EXPLANATORY
CODE: SELECT 7+1;

OUTPUT:
        +-----+
        | 7+1 |
        +-----+
        |   8 |
        +-----+
===================================================================================================

CODE: SELECT 7 % 2;

OUTPUT:
        +-------+
        | 7 % 2 |
        +-------+
        |     1 |
        +-------+

SHOW AND DESCRIBE:

xxxxxxxxxx
SYNTAX: SHOW TABLES

Shows the list of tables inside the current database

OUTPUT: 
        +--------------+
        | Tables_in_db |
        +--------------+
        | records      |
        +--------------+
===================================================================================================        
SYNTAX: DESCRIBE table_name or DES table_name;

EXAMPLE: DESCRIBE records

NOTE: WE CAN ALSO USE DES INSTEAD OF DESCRIBE

OUTPUT:
        +--------------+-------------+------+-----+---------+-------+
        | Field        | Type        | Null | Key | Default | Extra |
        +--------------+-------------+------+-----+---------+-------+
        | sno          | int         | YES  |     | NULL    |       |
        | student_name | varchar(10) | YES  |     | NULL    |       |
        | email        | varchar(20) | YES  |     | NULL    |       |
        | year         | int         | YES  |     | NULL    |       |
        | column_name  | int         | YES  |     | NULL    |       |
        +--------------+-------------+------+-----+---------+-------+

ALIASING:

xxxxxxxxxx
#USING ALIAS

SYNTAX: SELECT column_name as alias_name  /*USE OF AS IS OPTIONAL */
        FROM table_name;

EXAMPLE: SELECT year as this_will_display_instead_of_year
        FROM records;

**NOTE: Alias name does not change the actual column name. Orginal column name remains the same**

        
OUTPUT:
        +-----------------------------------+
        | this_will_display_instead_of_year |
        +-----------------------------------+
        |                              2004 |
        |                              2003 |
        |                              2002 |
        |                              2004 |
        +-----------------------------------+
**NOTE: USE OF AS IS OPTIONAL. THE BELOW CODE WILL YIELD TTHE SAME RESULT AS THE ABOVE ONEE.
ALSO THERE SHOULD BE NO SPACE IN BETWEEN IF WE USE ALIAS NAME WITHOUT QUOTES.

SELECT year as y FROM records (OR) SELECT year y FROM records (OR) SELECT year 'y' FROM records
            --------                          ----                             ----
===================================================================================================

#ALIASING MULTIPLE COLOUMN NAMES

SYNTAX: SELECT column_name AS new_name, another_column_name as another_new_name
        FROM table_name;

CODE: SELECT student_name as name , year as ' birth year' 
      FROM records;

TABLE ALIASES( PREREQUSITE : JOINS)

xxxxxxxxxx
#TABLE ALIASES

LIKE COLUMN ALIASES WE CAN HAVE ALIAS NAME FOR TABLES TOO

SYNTAX:  SELECT table_alias_1.coloumn_name , table_alias_2
        FROM tabel_name_1 table_alias_1 , table_name_2 table_alias_2;

CONSIDER THE TABLES BELOW

                                    TABLE NAME: records
                +-----+--------------+------------------+------+-------------+
                | sno | student_name | email            | year | column_name |
                +-----+--------------+------------------+------+-------------+
                |   1 | ram          | ram@gmail.com    | 2004 |          10 |
                |   2 | sam          | sam@yahoo.com    | 2003 |          20 |
                |   3 | hari         | hari@outlook.com | 2002 |          30 |
                |   4 | ramu         | ramu@gmail.com   | 2004 |          20 |
                +-----+--------------+------------------+------+-------------+


                                    TABLE NAME: test_table

                                    +------+------+---------+
                                    | name | year | present |
                                    +------+------+---------+
                                    | ram  | 2004 | NULL    |
                                    | NULL | NULL | NULL    |
                                    | sam  | 2001 | absent  |
                                    +------+------+---------+  
 
                                   TABLE NAME: test_table_3
                    +-----+--------------+------------------+---------------+-------------+
                    | sno | student_name | email            | year_of_birth | column_name |
                    +-----+--------------+------------------+---------------+-------------+
                    |   1 | ram          | ram@gmail.com    |          2004 |          10 |
                    |   2 | sam          | sam@yahoo.com    |          2003 |          20 |
                    |   3 | hari         | hari@outlook.com |          2002 |          30 |
                    |   4 | ramu         | ramu@gmail.com   |          2004 |          20 |
                    +-----+--------------+------------------+---------------+-------------+

CODE: SELECT a1.student_name , a2.year_of_birth
      FROM records a1 , test_table_3 a2;

OUTPUT:
        +--------------+---------------+
        | student_name | year_of_birth |
        +--------------+---------------+
        | ramu         |          2004 |
        | hari         |          2004 |
        | sam          |          2004 |
        | ram          |          2004 |
        | ramu         |          2003 |
        | hari         |          2003 |
        | sam          |          2003 |
        | ram          |          2003 |
        | ramu         |          2002 |
        | hari         |          2002 |
        | sam          |          2002 |
        | ram          |          2002 |
        | ramu         |          2004 |
        | hari         |          2004 |
        | sam          |          2004 |
        | ram          |          2004 |
        +--------------+---------------+

#WE CAN AVOID THE ABIVE SITUATION USING WHERE CLAUSE

CODE: SELECT a1.student_name , a2.year_of_birth
      FROM records a1 , test_table_3 a2
      WHERE a1.sno = a2.sno;

OUTPUT:
        +--------------+---------------+
        | student_name | year_of_birth |
        +--------------+---------------+
        | ram          |          2004 |
        | sam          |          2003 |
        | hari         |          2002 |
        | ramu         |          2004 |
        +--------------+---------------+

WE CAN ALSO OTHER CONDITION WITH WHERE CLAUSE

COMMENTS

xxxxxxxxxx
#LIKE PYTHON WE CAN USE COMMENTS

/* THIS IS A COMMENT */

WHERE

xxxxxxxxxx
#GENERAL SYNTAX

SELECT coloumn_name,
FROM table_name,
WHERE condition;

NOTE: THE FOLLOWING OPERATORS CAN BE USED IN PLACE OF CONDITION

RELATIONAL OPERATORS

xxxxxxxxxx
#USING RELATIONAL OPERATORS


# = (EQUALITY OPERATOR LIKE == IN PYTHON)   

/*  Q: DISPLAY THE RECORDS OF THE STUDENTS WHOSE NAME IS RAM  */

CODE: SELECT *
      FROM records
      WHERE student_name = 'ram';

OUTPUT:
        +------+--------------+---------------+------+-------------+
        | sno  | student_name | email         | year | column_name |
        +------+--------------+---------------+------+-------------+
        |    1 | ram          | ram@gmail.com | 2004 |          10 |
        +------+--------------+---------------+------+-------------+
================================================================================================

# >(greater than)  , <(lesser than) , >=(greater than or equal to)  , <=(less than or equal to)

/*  Q: DISPLAY THE RECORDS OF THE STUDENT WHOSE BIRTH YEAR IS LESS THAN OR EQUAL TO 2003  */

CODE: SELECT * 
      FROM records
      WHERE year <= 2003;

OUTPUT:

        +------+--------------+------------------+------+-------------+
        | sno  | student_name | email            | year | column_name |
        +------+--------------+------------------+------+-------------+
        |    2 | sam          | sam@yahoo.com    | 2003 |          20 |
        |    3 | hari         | hari@outlook.com | 2002 |          30 |
        +------+--------------+------------------+------+-------------+

================================================================================================

# <> or (!== --> NOT VALID IN MYSQL 8.0)

/*  Q: DISPLAY THE RECORDS OF THE STUDENT WHOSE BIRTH YEAR IS NOT EQUAL TO 2003  */

CODE: SELECT * 
      FROM records
      WHERE year <> 2003;

OUTPUT:
        +------+--------------+------------------+------+-------------+
        | sno  | student_name | email            | year | column_name |
        +------+--------------+------------------+------+-------------+
        |    1 | ram          | ram@gmail.com    | 2004 |          10 |
        |    3 | hari         | hari@outlook.com | 2002 |          30 |
        |    4 | ramu         | ramu@gmail.com   | 2004 |          20 |
        +------+--------------+------------------+------+-------------+

BETWEEN

xxxxxxxxxx
#BETWEEN 

SYNTAX: SELECT *
        FROM table_name
        WHERE column_name BETWEEN lower_limit AND upper_limit;  #inclusive of upper_limit and lower_limit

/*  Q: DISPLAY THE RECORDS OF THE STUDENT WHOSE BIRTH YEAR IS IN THE RANGE OF 2002 TO 2004 */ 


SYNTAX: SELECT *
        FROM records
        WHERE year BETWEEN 2002 AND 2004;  #includes both 18 and 22 

OUTPUT:
        +------+--------------+------------------+------+-------------+
        | sno  | student_name | email            | year | column_name |
        +------+--------------+------------------+------+-------------+
        |    1 | ram          | ram@gmail.com    | 2004 |          10 |
        |    2 | sam          | sam@yahoo.com    | 2003 |          20 |
        |    3 | hari         | hari@outlook.com | 2002 |          30 |
        |    4 | ramu         | ramu@gmail.com   | 2004 |          20 |
        +------+--------------+------------------+------+-------------+

LIKE

xxxxxxxxxx
#LIKE(only text[string] values)      ---> USING WILDCARDS(REGEX)  --> PATTERNS(REGEX)
#'a%' ---> starting with a
#'%a' ---> ending with a
#'%a%' ---> contains a 
#% --> wildcard
#a% --> pattern

xxxxxxxxxx
/*  SELECT THE NAME OF THE STUDENT [STARTING] WITH THE LETTER R */ 

CODE: SELECT *
      FROM records
      WHERE student_name LIKE 'r%';

OUTPUT:
        +------+--------------+----------------+------+-------------+
        | sno  | student_name | email          | year | column_name |
        +------+--------------+----------------+------+-------------+
        |    1 | ram          | ram@gmail.com  | 2004 |          10 |
        |    4 | ramu         | ramu@gmail.com | 2004 |          20 |
        +------+--------------+----------------+------+-------------+

#** Note r% --> will select both r and R ** 

'%r%' ---> WILL SELECT ALL THE RECORS CONTAINING THE LETTER r or R -> (CAN BE START OR IN END AND CAN BE IN BETWEEN)

'%r' ---> WILL SELECT ALL THE RECORDS WHICH ENDS WITH LETTER r or R  

Empty set (0.07 sec) ---> DISPLAYED WHEN IT FINDS NO MATCHING RECORDS(
                                                 SELECT * FROM records WHERE student_name LIKE 'r%';)

===================================================================================================

/* Q:  DISPLAY THE RECORDS THE STUDENTS IF THE LENGTH OF THE NAME OF STUDENT IS EXACTLY THREE CHARACTERS */

CODE: SELECT *
      FROM records
      WHERE student_name LIKE '___';

"___" ---> There are three underscore(_) inside the quotes(" ") 
       ---> Three underscores are used to match any string with exactly three charcaters 
       ---> Underscore here represents characters( four underscore matches any sting with exactly 4 characters ) 

OUTPUT:
        +------+--------------+---------------+------+-------------+
        | sno  | student_name | email         | year | column_name |
        +------+--------------+---------------+------+-------------+
        |    1 | ram          | ram@gmail.com | 2004 |          10 |
        |    2 | sam          | sam@yahoo.com | 2003 |          20 |
        +------+--------------+---------------+------+-------------+

===================================================================================================

#DIFFERENCE BETWEEN '___' AND '___%'

'___' ---> MATCHES ANY STRING WHICH HAS EXACTLY THREE CHARACTERS

'___%' ---> MATCHES ANY STRING WHICH HAS AT LEAST THREE CHARACTERS

CODE: SELECT *
      FROM records
      WHERE student_name LIKE '___%';

OUTPUT: 

        +------+--------------+------------------+------+-------------+
        | sno  | student_name | email            | year | column_name |
        +------+--------------+------------------+------+-------------+
        |    1 | ram          | ram@gmail.com    | 2004 |          10 |
        |    2 | sam          | sam@yahoo.com    | 2003 |          20 |
        |    3 | hari         | hari@outlook.com | 2002 |          30 |
        |    4 | ramu         | ramu@gmail.com   | 2004 |          20 |
        +------+--------------+------------------+------+-------------+

IN

xxxxxxxxxx
#IN (MEMBERSHIP)

/*Q:  DISPLAY THE RECORDS OF THE STUDENT IF A STUDENT IS BORN IN THE YEAR 2002 AND 2004 */

CODE: SELECT *
      FROM records
      WHERE year IN (2002,2004);

OUTPUT:
        +------+--------------+------------------+------+-------------+
        | sno  | student_name | email            | year | column_name |
        +------+--------------+------------------+------+-------------+
        |    1 | ram          | ram@gmail.com    | 2004 |          10 |
        |    3 | hari         | hari@outlook.com | 2002 |          30 |
        |    4 | ramu         | ramu@gmail.com   | 2004 |          20 |
        +------+--------------+------------------+------+-------------+

LOGICAL OPERATOR

xxxxxxxxxx
#LOGICAL OPERATOR (AND  ,  OR)

#AND (BOTH THE STATEMENTNS MUST BE TRUE)

/*  Treat AND as * (LOGICAL MULTIPLICATION)
         True as 1
         False as 0
         
         True AND True ---> 1 * 1 ---> 1 ---> True
         True AND False ---> 1 * 0 ---> 0 ---> False
          False AND True ---> 0 * 1 ---> 0 ---> False
          False AND False ---> 0 * 0 ---> 0 ---> False

         Anything multiplied by zero is zero so if there

    Treat OR as + (LOGICAL ADDITION)
        
         True OR True ---> 1 + 1 ---> 1 ---> True   /* here 1+1 --> 1 (still True) */
         True OR False ---> 1 + 0 ---> 1 ---> True
          False OR True ---> 0 + 1 ---> 1 ---> True
          False OR False ---> 0 + 0 ---> 1 ---> False

*/

xxxxxxxxxx
#AND (BOTH THE STATEMENTNS MUST BE TRUE)
/* Q: DISPLAY THE REOCORDS OF THE STUDENTS IF THEIR NAME STARTS WITH R AND THEIR BIRTH YEAR IS GREATER THAN OR EQUAL TO2003 */

CODE: SELECT *
      FROM records
      WHERE student_name LIKE 'r%' 
      AND year >= 2003;

OUTPUT:
        +------+--------------+----------------+------+-------------+
        | sno  | student_name | email          | year | column_name |
        +------+--------------+----------------+------+-------------+
        |    1 | ram          | ram@gmail.com  | 2004 |          10 |
        |    4 | ramu         | ramu@gmail.com | 2004 |          20 |
        +------+--------------+----------------+------+-------------+
        

xxxxxxxxxx
#OR (AT LEAST ONE OF THE STATEMENT SHOULD BE TRUE) 

/* Q: DISPLAY THE REOCORDS OF THE STUDENTS IF THEIR NAME STARTS WITH R OR IF THEIR BIRTH YEAR IS GREATER THAN OR EQUAL TO 2003 */

CODE: SELECT *
      FROM records
      WHERE student_name LIKE 'r%'
      OR year >= 2003;

OUTPUT:
        +------+--------------+----------------+------+-------------+
        | sno  | student_name | email          | year | column_name |
        +------+--------------+----------------+------+-------------+
        |    1 | ram          | ram@gmail.com  | 2004 |          10 |
        |    2 | sam          | sam@yahoo.com  | 2003 |          20 |
        |    4 | ramu         | ramu@gmail.com | 2004 |          20 |
        +------+--------------+----------------+------+-------------+

xxxxxxxxxx
#SIMILARITY BETWEEN OR AND IN

CODE 1: SELECT *
        FROM records
        WHERE year = 2002 OR year = 2003 OR year 2004;

/* THE ABOVE QUERY CAN ASLO BE WRITTEN USING THE *IN* OPERATOR  */
       
CODE 2: SELECT *
        FROM records
        WHERE year IN (2002 , 2003 , 2004);

xxxxxxxxxx
#NOT

CODE: SELECT *
      FROM records
      WHERE student_name NOT LIKE '%a'; /* SELECTS ALL THE RECORDS WHOSE NAME DOESN'T START WITH A */

OUTPUT:
        +------+--------------+------------------+------+-------------+
        | sno  | student_name | email            | year | column_name |
        +------+--------------+------------------+------+-------------+
        |    1 | ram          | ram@gmail.com    | 2004 |          10 |
        |    2 | sam          | sam@yahoo.com    | 2003 |          20 |
        |    3 | hari         | hari@outlook.com | 2002 |          30 |
        |    4 | ramu         | ramu@gmail.com   | 2004 |          20 |
        +------+--------------+------------------+------+-------------+
================================================================================================

CODE: SELECT *
      FROM records
      WHERE year NOT IN (2004,2002) ;  /* SELECTS ALL THE RECORDS EXCEPT 2004 AND 2002 */
      
OUTPUT:
        +------+--------------+---------------+------+-------------+
        | sno  | student_name | email         | year | column_name |
        +------+--------------+---------------+------+-------------+
        |    2 | sam          | sam@yahoo.com | 2003 |          20 |
        +------+--------------+---------------+------+-------------+    
      ================================================================================================

CODE: SELECT *
      FROM records
      WHERE year NOT BETWEEN 2002 AND 2004; /* SELECTS ALL THE RECORD EXCEPT 2002 , 2003 AND 2004 */

OUTPUT:
        Empty set


# NOT LIKE . IS NOT and so on 

ORDER BY

xxxxxxxxxx
# ORDER BY

#TO ORDER THE VALUES OF A COLUMN BASED ON ANOTHER COLUMN

#NOTE : ORDER BY orders the value in ascending(ASC) order by default.

CODE: SELECT *
      FROM records
      ORDER BY year; #ASC ---> ORDER BY year ASC;

OUTPUT:
        +------+--------------+------------------+------+-------------+
        | sno  | student_name | email            | year | column_name |
        +------+--------------+------------------+------+-------------+
        |    3 | hari         | hari@outlook.com | 2002 |          30 |
        |    2 | sam          | sam@yahoo.com    | 2003 |          20 |
        |    1 | ram          | ram@gmail.com    | 2004 |          10 |
        |    4 | ramu         | ramu@gmail.com   | 2004 |          20 |
        +------+--------------+------------------+------+-------------+

=================================================================================================


#To order an COLUMN which has only words(strings to be specific)

CODE: SELECT *
      FROM records
       ORDER BY student_name; #orders in alphabetcial order in ASC

OUTPUT:
        +------+--------------+------------------+------+-------------+
        | sno  | student_name | email            | year | column_name |
        +------+--------------+------------------+------+-------------+
        |    3 | hari         | hari@outlook.com | 2002 |          30 |
        |    1 | ram          | ram@gmail.com    | 2004 |          10 |
        |    4 | ramu         | ramu@gmail.com   | 2004 |          20 |
        |    2 | sam          | sam@yahoo.com    | 2003 |          20 |
        +------+--------------+------------------+------+-------------+
        
=================================================================================================

#To order in descending order

CODE: SELECT *
      FROM records
      ORDER BY year DESC; #orders DESC

OUTPUT:
        +------+--------------+------------------+------+-------------+
        | sno  | student_name | email            | year | column_name |
        +------+--------------+------------------+------+-------------+
        |    1 | ram          | ram@gmail.com    | 2004 |          10 |
        |    4 | ramu         | ramu@gmail.com   | 2004 |          20 |
        |    2 | sam          | sam@yahoo.com    | 2003 |          20 |
        |    3 | hari         | hari@outlook.com | 2002 |          30 |
        +------+--------------+------------------+------+-------------+

OPERATOR PRECEDENCE:

xxxxxxxxxx
INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN, MEMBER OF
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=

SOURCE: https://dev.mysql.com/doc/refman/8.0/en/operator-precedence.html

AGGREGATE FUNCTIONS:

xxxxxxxxxx
#AGGREGRATE FUNCTIONS aka GROUP FUNCTIONS aka MULTIPLE ROW FUNCTIONS

SYNTAX: SELECT aggregate_funtion_name(coloumn_name)
        FROM table_name;

So far we have only seen only one aggregate function(DISTINCT) if I am correct.
From now on we will learn about the other aggregate functions. 

MORE INFO ON AGGREGATE FUNTION CAN BE FOUND AT : https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

YOU CAN ALSO USE ALIAS WITH AGGREGATE FUNTIONS

xxxxxxxxxx
#MIN ---> RETURNS THE SMALLEST VALUE IN A COLUMN

CODE: SELECT MIN(year)
      FROM records;

OUTPUT:
        +-----------+
        | MIN(year) |
        +-----------+
        |      2002 |
        +-----------+

xxxxxxxxxx
#AGGREGATE FUNCTIONS--> THOSE WHICH RETURN SINGLE VALUE(MIN,MAX,AVG,SUM,etc..)

#MAX ---> RETURNS THE LARGEST VALUE IN A COLUMN

CODE: SELECT MAX(year)
      FROM records;

OUTPUT:
        +-----------+
        | MAX(year) |
        +-----------+
        |      2004 |
        +-----------+

xxxxxxxxxx
#AVG ---> TO FIND THE AVERAGE VALUE

average =  value_1 + value_2 + .... value_n
           ---------------------------------
                 no_of_values(n)

CODE: SELECT AVG(year)
      FROM records;

OUTPUT:
        +-----------+
        | AVG(year) |
        +-----------+
        | 2003.2500 |
        +-----------+

average = 2004 + 2003 + 2002 + 2004            =  8013       =  2003.2500 
          ---------------------------             -----
                        4                           4                  

xxxxxxxxxx
#COUNT --> TO COUNT THE NO ITEMS IN A COLUMN
#IT COUNTS ONLY NON-EMPTY( NULL TO BE SPECIFIC) VALUES  ---> IT DOESN'T TAKE ACCOUNT OF ANY NULL VALUES



CODE: SELECT COUNT(year)
      FROM records;

OUTPUT:
        +-------------+
        | COUNT(year) |
        +-------------+
        |           4 |
        +-------------+
       ====================================================================================================

#COUNT(*)

#COUNT(*) --> TO COUNT THE NUMBER OF ROWS IN A TABLE

CODE: SELECT COUNT(*)
      FROM records;

OUTPUT:
        +----------+
        | COUNT(*) |
        +----------+
        |        4 |
        +----------+
===================================================================================================


                  COUNT(coloumn_name) VS COUNT(*)

COUNT(coloumn_name)  ---> ONLY TAKES ACCOUNT OF NON NULL VALUES
COUNT(*)  ----> TAKES ACCOUNT OF NULL AND NON NULL VALUES i.e ALL VALUES


CONSIDER THE TABLE BELOW:

NAME OF THE TABLE: test_table
+------+------+---------+
| name | year | present |
+------+------+---------+
| ram  | 2004 | NULL    |
| NULL | NULL | NULL    |
| sam  | 2001 | absent  |
+------+------+---------+

CODE: SELECT COUNT(present) FROM test_table;
OUTPUT:
        +----------------+
        | COUNT(present) |
        +----------------+
        |              1 |
        +----------------+

CODE: SELECT COUNT(*) FROM test_table;
OUTPUT: 
        +----------+
        | COUNT(*) |
        +----------+
        |        3 |
        +----------+

xxxxxxxxxx
#SUM ---> RETURNS THE SUM OF ALL THE VALUES IN A COLUMN

CODE: SELECT SUM(year)
      FROM records;

OUTPUT:
        +-----------+
        | SUM(year) |
        +-----------+
        |      8013 |
        +-----------+

xxxxxxxxxx
# MORE ON DISTINCT AND ALL

DISTINCT CAN BE USED IN COMBINATION WITH OTHER AGGREGATE FUNCTIONS

MAX( DISTINCT column_name) , MIN( DISTINCT column_name) , AVG( DISTINCT column_name) ,
SUM( DISTINCT column_name) 

WHEN DISTINCT IS USED WITH SUM ONLY DUPLICATE ENTRIES ARE NOT TAKEN INTO ACCOUNT

CODE: SELECT SUM(DISTINCT year)
      FROM records;

OUTPUT:
        +--------------------+
        | SUM(DISTINCT year) |
        +--------------------+
        |               6009 |
        +--------------------+


#ALL vs DISTINCT VS COUNT---> COSIDER ALL THE VALUES

CONSIDER THE TABLE BELOW

NAME OF THE TABLE: test_table_2

+------+------+---------+
| name | year | present |
+------+------+---------+
| ram  | 2001 | NULL    |
| sam  | 2002 | present |
| ramu | 2003 | present |
| som  | 2004 | absent  |
+------+------+---------+
COUNT(column_name) ---> counts only non NULL values
COUNT(DISTINCT , column_name) ---->  counts only  distinct non NULL values
COUNT(ALL , column_name) ---> counts only non NULL values

CODE: SELECT COUNT(present) FROM test_table_2;

OUTPUT:
        +----------------+
        | COUNT(present) |
        +----------------+
        |              3 |    ----> present , present , absent
        +----------------+

CODE: SELECT COUNT(DISTINCT present) FROM test_table_2; 
OUTPUT:
        +-------------------------+
        | COUNT(DISTINCT present) |
        +-------------------------+
        |                       2 | ---> present , absent
        +-------------------------+
        
        ( present ,absent(duplicate present is not taken into account while counting)

CODE: SELECT COUNT(ALL present) FROM test_table_2;
OUTPUT:
        +--------------------+
        | COUNT(ALL present) |
        +--------------------+
        |                  3 |   -->  present , present , absent
        +--------------------+

MY SQL FUNCTIONS

STRING FUNTIONS:

xxxxxxxxxx
#CHAR() 

#NOTE: NEWER VERSIONS OF MYSQL INTERPRETS THE BINARY RESULT AS HEXADECIMAL
  
/*  TYPE status IN MYSQL COMMAND LINE CLIENT  */

Connection id:          11
Current database:       db
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Using delimiter:        ;
Server version:         8.0.27 MySQL Community Server - GPL
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal #THIS IS THE REASON WHY WE GET HEXADECIAML VALUE
Uptime:                 23 hours 35 min 0 sec


#TO SOLVE THIS PROBLEM FOLLOW THE STEPS BELOW THERE ARE TWO WAYS:

WAY 1:
    ONE QUICK FIX TO SOLVE THIS PROBLEM IS TO USE USING ASCII
    i.e..  SELECT CHAR(65 USING ASCII);
    SYNTAX: CHAR(N,... [USING charset_name])

MORE INFO CAN BE FOUND AT:https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_char

WAY 2:

STEP 1: GOTO TO THE FOLDER WHERE THE BIN FOLDER OF MYSQL SEREVER WHICH WILL BE LOCATED INSIDE MYSQL

IF YOU ARE USING WINDOWS 10 -->  C:\Windows\System32\cmd.exe ---> THIS WOULD BE PATH OF THE BIN FOLDER(IN MOST CASES IF NOT LOCATE THE FOLDER BY YOURSELF)

STEP 2: OPEN COMMAND PROMPT AND KEY IN THE BELOW COMMAND
        mysql -u root -p --skip-binary-as-hex

STEP 3: ENTER THE PASSWORD

STEP 4: RUN THE FOLLOWING 
        SELECT CHAR(65)

/* NOTE YOU NEED TO DO THIS STEP EVERY TIME BEFORE YOU USE MYSQL AND MYSQL CLI CAN BE USED DIRECTLY FROM CMD(COMMAND PROMPT) */

MORE INFO CAN BE FOUND AT: https://bugs.mysql.com/bug.php?id=99480

CODE: SELECT CHAR(65 USING ASCII) AS "Alphabet";

OUTPUT:
        +----------+
        | Alphabet |
        +----------+
        | A        |
        +----------+

=====================================================================================================

#CONCAT()

-> IF COLOUMN NAMES ARE USED THE RECORDS GETS CONCATENATED

CODE: SELECT CONCAT(student_name , year) as "NAME AND YEAR"
      FROM records;

OUTPUT:
        +---------------+
        | NAME AND YEAR |
        +---------------+
        | ram2004       |
        | sam2003       |
        | hari2002      |
        | ramu2004      |
        +---------------+

-> CAN ALSO BE USED TO CONCAT TWO STRINGS
===================================================================================================

CODE: SELECT CONCAT("SIBI" , "RAJ") AS "NAME";

OUTPUT:
        +---------+
        | NAME    |
        +---------+
        | SIBIRAJ |
        +---------+
===================================================================================================

CODE: SELECT "SI" "BI" "RAJ";
    
OUTPUT:
        +---------+
        | SI      |
        +---------+
        | SIBIRAJ |
        +---------+
=================================================================================================   

#LOWER()/LCASE()  ---> https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_lcase

CODE: SELECT LOWER("PYTHON");

OUTPUT:
        +-----------------+
        | LOWER("PYTHON") |
        +-----------------+
        | python          |
        +-----------------+
=================================================================================================

#UPPER()/LCASE() 

CODE: SELECT UPPER("python");

OUTPUT:
        +-----------------+
        | UPPER("python") |
        +-----------------+
        | PYTHON          |
        +-----------------+
=================================================================================================

# TRIM ---> REMOVES TRAILING AND LEADING SPACES --> LIKE STRIP()
# LTRIM ---> LIKE LSTRIP()
# RTIRM ---> LIKE RSTIP()


CODE: SELECT TRIM("  AKA STRIP  ");

OUTPUT:
        +-----------------------+
        | TRIM("  AKA STRIP  ") |
        +-----------------------+
        | AKA STRIP             |
        +-----------------------+
=================================================================================================

#SUBSTRING()/SUBSTR()

SUBSTR(given_string,3,4) ---> SELECT 4 CHARACTERS STARING FROM THE INDEX 3

LEFT SIDE INDEX STARTS FROM 0  AND RIGHT SIDE INDEX STARTS FROM -1

CODE: SELECT SUBSTR("0123456789" , 3 , 4) AS SLICING;

OUTPUT:
        +---------+
        | SLICING |
        +---------+
        | 2345    |
        +---------+