ADVANCE SQL

OPERATORS-
LOGICAL, ARITHMETIC, COMPARISON and BIT OPERATORS

Operators are the building blocks of complex queries

• Logical Operator
• Logical operators are AND and OR. This operators allow you to relate numbers  of conditions in various ways. Either true or false

• Arithmetic Operators
• It is used to perform basic math operations (+,-,/,*,%)

• Comparison Operators
• The comparison operators where used when making comparisons between values
• These are =, != or < >, > , < , >= , <=>, IS NULL, IS NOT NULL, BETWEEN, REGEXP, RLIKE, NOT REDEXP, NOT LIKE.

PERFORMING TRANSACTION with BEGIN and COMMIT

BEGIN TRANSACTION

• If an error encountered, and the modification is made after the begin transaction can set to rolled back to return the data to know state of consistency 

SYNTAX:

Begin;

COMMIT TRANSACTION

• This will instruct that the modifications made where permanent part of database

NOTE: ROLLBACK means restoring original value present before update query

RUNNING MYSQL IN BATCH FILE 

Running MYSQL queries without entering the mysql client itself.
Storing mysql queries into a text file and then run the text file through mysql

SYNTAX:

mysql -u username -p database <  '/path/to/filename.txt'

VIEW

A specific look on a data or more tables
It contains rows and columns just like table

SYNTAX:

CREATE VIEW            view_name
AS  SELECT                   column_name(s)
FROM                            table_name
WHERE                         condition

LOCK TABLES

Lock tables enables you to prevent other sessions from modifying tables during periods when a session required exclusive access to them. Lock may be used to emulate transactions or to get more speed when updating.

NOTE: Table locks protects only against inappropriate read or write. A session with another purposes like drop table can be acquired. 

SYNTAX: 

           LOCK TABLE      [tablename]      READ;

UNLOCK TABLES

Release any table locks held by the current session.

SYNTAX:
         UNLOCK TABLES;

DATABASE BACKUPS

Backup - the main reason why it is needed or use to backup your database is to recover data if unexpected deletion may occur.

Backup Syntax:

 

BACKUP TABLE    [table_name]   to ‘[path]’;

Restore 

 On the other hand restore is used to return data to its original condition or getting  or copying the data to the database;

SYNTAX:

RESTORE TABLE [table_name]  from '[path]';

USING SELECT ... INTO  

   Another SELECT form which enables a query to be written  to a file

• SELECT ... INTO OUTFILE
• It writes a selected row to a file 

            SYNTAX:

                          SELECT                                      [name_of_column]      
                          INTO OUTFILE                          'file_name'
                          LINES TERMINATED BY       'terminator'
                          FROM                                          [table_name]

 All the records inside column name where copied in the col_name.txt file

• SELECT ... INTO DUMPFILE
• Writes a single row to a file

           SYNTAX:
             SELECT                        [row_name]
             INTO   DUMPFILE      [file_name_to_be_written]
             FROM                            [table_name]
             LIMIT                            [no_of_limit]

NOTE:  Disregarding LIMIT will cause an error especially if the column consist of numbers of records because select...into dump writes only a single row to a file without nothing the numbers of row to be copied in a file you will encounter error but still there will be a record that will be copied but only the first row of the records.

SELECT ... INTO var_list

• Select column values and into  variables

   SYNTAX :
          SELECT                           [column_name]
          INTO                               @[variable_name]
          FROM                              [table_name];

USING LOAD DATA INTO INFILE:

Use to read the file back to the table

SYNTAX:
    LOAD DATA INFILE           'filename'
    INTO TABLE                         [table_name];

NOTE: By the use of fields terminated by and starting by, the records that was read in the file was divided into different rows.



MYSQL DUMP

•      A backup program, that is use to dump the database, or it is a collection of database for backup. It contains SQL statements to create table, populate it or both.

SYNTAX:

• DUMP ALL DATABASE

                    mysql\bin> mysqldump --all-databases > dump.sql

• DUMP SINGLE DATABASE

                     mysql\bin> mysqldump -u root --databases clsu > dump.sql

•  OK omit --databases in single database 

                       mysql\bin> mysqldump -u root clsu > dump.sql

• Dump specific table 

                       mysqldump -u root clsu college> dump.sql

MYSQLHOTCOPY                 



    Runs only on UNIX and NetWare, it is used  for backing up MYISAM and ARCHIEVE.

NOTE: Must have select privilege, reload  privilege, and lock tables  privilege




SYNTAX:   

shell> mysqlhotcopy db_name [/path/to/new_directory]

shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

Backing Innodb

 Two ways in backing Innodb

                                  using mysqldump 

                                  copy the file

•Too copy file shutdown MySQL server make sure no errors

•Copy all InnoDB data file (ibdata file and .ibd file) to safe place

•Copy all .frm file

•Copy all InnoDB log file

•Copy you’re my.cnf configuration

Recover Innodb

1. Run Mysql server with binary log on defore taking the buckup

2. To achieve point-in-time recovery you can apply changes from binary log

3. To recover from mysql crash just restart it and the Innodb automatically logs and perform rolls back up to present

SQL Queries

Brief discussion about the history of SQL

                SQL original name was “Structured English Query Language” or SEQUEL, developed by IBM around 1975, and then became SEQUEL/2 then SQL.

Simple/Basic query used in SQL are Select, Project and Join

SQL Select – the select statement used for retrieval queries in SQL. Basic SQL select is the SELECT –FROM-WHERE block

Example: Let’s say we have a company database with a table employee, under this table are Fname, Minit, Lname, SSN, Bdate, Address . Inside the table is a record of employee named Franklin T. Wong, and we want to retrieve it Birthdate and address we can use this syntax:

                SELECT                 BDATE, ADDRESS
                FROM                   EMPLOYEE
                WHERE                 FNAME='Franklin' AND MINIT='T’
                                                AND LNAME='Wong’;

Using ALIASES :
The use of aliases allows some queries who need to refer to the same relation twice. Aliases will handle the relation name.

Example: Let us again use the company database. with additional attributes like 

salary, superssn,dno. Now retrieve the employee's name and the name  of his or her  immediate supervisor.

                                               SELECT         E.FNAME, E.LNAME, S.FNAME,   S.LNAME
                                        FROM            EMPLOYEE AS E, EMPLOYEE AS S
                                        WHERE          E.SUPERSSN=S.SSN;

No Where -clause

Disregarding the use of where clause means there is no condition under the given query, all the relations inside the FROM-clause where selected. unused WHERE-clause means WHERE TRUE.

Example: On the company database lets retrieve the fname of the table employee without using specifying the fname to include in the retrieval.

                                           SELECT   FNAME
                                            FROM     EMPLOYEE;

 

USE * :
When we use * means we are selecting all attributes of a given table.

Example: On the company database, let us retrieve all the attribute of the table employee having a specific DNO lets say DNO=4.
                                      SELECT    *
                                      FROM        EMPLOYEE
                                      WHERE     DNO=4;

USE of DISTINCT

In SQL it treat relation as a set. In some cases many tuples are being duplicated , in order to eliminate this and use or retrieve only those relation once with a duplicate or not we use the keyword DISTINCT.

Example: In database company lets first print out or retrieve  the salary of employee as the usual kind of retrieving: "select salary from employee;"

---------- you can see on the print that the salary being printed that some has duplicate.

now to eliminate this let us use DISTINCT

                                    SELECT      SALARY
                                    FROM          DISTINCT EMPLOYEE;

USE OF OPERATIONS :

Some of SQL operations are UNION, set DIFFERENCE or MINUS and Intersection (INTERSECT). In using this operations there are some exception that need to remember. The two relations must have the same attributes and the attributes must appear in the same order.

Example: Printout all the project numbers that involve an employee whose last name is 'Smith' worker or manager of a department that controls the project.

SYNTAX:
                           SELECT       PNAME
                           FROM           PROJECT, DEPARTMENT, EMPLOYEE
                           WHERE        DNUM=DNUMBER  and MGRSSN=SSN
                            AND             LNAME='Smith'
                           UNION          (SELECT PNAME
                           FROM             PROJECT,WORKS_ON, EMPLOYEE
                           WHERE       PNUMBER=PNO AND ESSN=SSN AND LNAME='SMITH');

NESTED QUERIES:

Nested queries is a complete SELECT query, nested queries can be specified within the WHERE-clause of another query which we called outer query

CORRELATED QUERY

The result of a correlated nested query is different for each tuple (or combination of tuples) of the relation(s) the outer query

EXISTS CLAUSE

The exist clause is used in checking correlated nested query is empty or not

EXPLICIT SETS 

Using an set of values in where clause rather than in nested query

NULL

 It is allowed in SQL query to check of the values where null

JOIN RELATIONS 

 Under the WHERE clause join relation can be used. Join in mysql means query data from two or more tables.

AGGREGATE FUNCTIONS 

It allows or let you you to return a single value after the performing a calculation on a set of records

GROUPING


            GROUP BY-clause is used in grouping attributes. It can be used in aggregate functions and also in SELECT-clause