What is SQL? What can SQL DO?

SQL BACKGROUND :

SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s.[13] This version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM’s original database management system

In the late 1970s, Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Codd, Chamberlin, and Boyce, and developed their own SQL-based RDBMS

WHAT IS SQL ??? :

  • SQL (pronounced “ess-que-el”) stands for Structured Query Language. SQL is used to communicate with a database
  • It is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.
  • The data in RDBMS is stored in database objects called tables.
  • A table is a collection of related data entries and it consists of columns and rows.
  • The standard SQL commands such as:

   “Select

                                                                    “Insert”,

                                                                 –   “Update”,

                                                                 –   “Delete”,

                                                                 –   “Create”,

                                                                 –   Drop“

can be used to accomplish almost everything that one needs to do with a database

 

What can Sql do ???

  • SQL can execute queries against a database
  • SQL can retrieve data from a database
  • SQL can insert and update records in a database
  • SQL can delete records from a database
  • SQL can create new databases
  • SQL can create new tables in a database
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views

SQL QUERY : JOINS

  • What Is a Query?
  • A query is an inquiry into the database using the SELECT statement. A query is used to extract data from the database in a readable format according to the user’s request.
  • There are four keywords, or clauses, that are valuable parts of a SELECT statement. These keywords are as follows:
  • SELECT
  • FROM
  • WHERE
  • ORDER BY
  • A database most often contains one or more tables. Each table is identified by a name (e.g. “Customers” or “Orders”). Tables contain records (rows) with data.

A SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them

SQL : JOIN

  • A SQL join is a Structured Query Language (SQL) instruction to combine data from two sets of data (e.g. two tables)
  • Different types of joins:
  • Inner Join
  • Left join
  • Right join
  • Full join

INNER JOIN

  • The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.
  • SQL INNER JOIN Syntax
  • SELECT column_name(s)
    FROM table1
    INNER JOIN table2
    ONtable1.column_name=table2.column_name;
  • PS!INNER JOIN is the same as JOIN.

 

Inner join example :

Select first_name, last_name, order_date, order_amount

from customers c

inner join orders o

on c.customer_id = o.customer_id

customer_id first_name last_name email address city state zipcode
1 George Washington gwashington@usa.gov 3200 Mt Vernon Hwy Mount Vernon VA 22121
2 John Adams jadams@usa.gov 1250 Hancock St Quincy MA 02169
3 Thomas Jefferson tjefferson@usa.gov 931 Thomas Jefferson Pkwy Charlottesville VA 22902
4 James Madison jmadison@usa.gov 11350 Constitution Hwy Orange VA 22960
5 James Monroe jmonroe@usa.gov 2050 James Monroe Parkway Charlottesville VA 22902

 

order_id order_date amount customer_id
1 07/04/1776 $234.56 1
2 03/14/1760 $78.50 3
3 05/23/1784 $124.00 2
4 09/03/1790 $65.50 3

 

first_name last_name order_date order_amount
George Washington 07/4/1776 $234.56
John Adams 05/23/1784 $124.00
Thomas Jefferson 03/14/1760 $78.50
Thomas Jefferson 09/03/1790 $65.50

Left join…

  • The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
  • Left join Syntax:
  • SELECT column_name(s)
    FROM table1
    LEFT JOIN table2
    ONtable1.column_name=table2.column_name;

PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.

 

LEFT JOIN EXAMPLE

customer_id first_name last_name email address city state zipcode
1 George Washington gwashington@usa.gov 3200 Mt Vernon Hwy Mount Vernon VA 22121
2 John Adams jadams@usa.gov 1250 Hancock St Quincy MA 02169
3 Thomas Jefferson tjefferson@usa.gov 931 Thomas Jefferson Pkwy Charlottesville VA 22902
4 James Madison jmadison@usa.gov 11350 Constitution Hwy Orange VA 22960
5 James Monroe jmonroe@usa.gov 2050 James Monroe Parkway Charlottesville VA 22902

 

first_name last_name order_date order_amount
George Washington 07/04/1776 $234.56
John Adams 05/23/1784 $124.00
Thomas Jefferson 03/14/1760 $78.50
Thomas Jefferson 09/03/1790 $65.50
James Madison NULL NULL
James Monroe NULL NULL

 RIGHT JOIN :

  • The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
  • Right join Syntax:
  • SELECTcolumn_name(s)
    FROM table1
    RIGHT JOIN table2
    ON table1.column_name=table2.column_name;

PS! In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

 

RIGHT JOIN EXAMPLE…

Select first_name, last_name, order_date, order_amount

from customers c

right join orders o on

c.customer_id = o.customer_id

customer_id first_name last_name email address city state zipcode
1 George Washington gwashington@usa.gov 3200 Mt Vernon Hwy Mount Vernon VA 22121
2 John Adams jadams@usa.gov 1250 Hancock St Quincy MA 02169
3 Thomas Jefferson tjefferson@usa.gov 931 Thomas Jefferson Pkwy Charlottesville VA 22902
4 James Madison jmadison@usa.gov 11350 Constitution Hwy Orange VA 22960
5 James Monroe jmonroe@usa.gov 2050 James Monroe Parkway Charlottesville VA 22902

 

order_id order_date amount customer_id
1 07/04/1776 $234.56 1
2 03/14/1760 $78.50 3
3 05/23/1784 $124.00 2
4 09/03/1790 $65.50 3

 

first_name last_name order_date order_amount
George Washington 07/04/1776 $234.56
Thomas Jefferson 03/14/1760 $78.50
John Adams 05/23/1784 $124.00
Thomas Jefferson 09/03/1790 $65.50
NULL NULL 07/21/1795 $25.50
NULL NULL 11/27/1787 $14.40

FULL JOIN….

  • The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).
  • The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
  • Full Join Syntax:
  • SELECT column_name(s)
    FROM table1
    FULL OUTER JOIN table2
    ON table1.column_name=table2.column_name;

PS! In some databases FULL JOIN is called FULL OUTER JOIN

 

FULL JOIN EXAMPLE

Select first_name, last_name, order_date, order_amount

from customers c

full join orders o

on c.customer_id = o.customer_id

customer_id first_name last_name email address city state zipcode
1 George Washington gwashington@usa.gov 3200 Mt Vernon Hwy Mount Vernon VA 22121
2 John Adams jadams@usa.gov 1250 Hancock St Quincy MA 02169
3 Thomas Jefferson tjefferson@usa.gov 931 Thomas Jefferson Pkwy Charlottesville VA 22902
4 James Madison jmadison@usa.gov 11350 Constitution Hwy Orange VA 22960
5 James Monroe jmonroe@usa.gov 2050 James Monroe Parkway Charlottesville VA 22902

 

order_id order_date amount customer_id
1 07/04/1776 $234.56 1
2 03/14/1760 $78.50 3
3 05/23/1784 $124.00 2
4 09/03/1790 $65.50 3

 

first_name last_name order_date order_amount
George Washington 07/04/1776 $234.56
Thomas Jefferson 03/14/1760 $78.50
John Adams 05/23/1784 $124.00
Thomas Jefferson 09/03/1790 $65.50
NULL NULL 07/21/1795 $25.50
NULL NULL 11/27/1787 $14.40
James Madison NULL NULL
James Monroe NULL NULL

conclusion:

  • The SQL queries are the most common and essential SQL operations. Via an SQL query, one can search the database for the information needed. SQL queries are executed with the “SELECT” statement
  • The four basic SQL joins described above let you tie the different pieces of data together, and allow you to start asking and answering more challenging questions.
  • Statements: In database systems the SQL statements are used for sending queries from a client program to a server where the databases are stored.
  • Queries: A query will retrieve data, based on a given criteria
  • Clauses: The clauses are components of the statements and the queries

Write a Reply or Comment

Your email address will not be published.