SQL

Click Here for SQL Tutorial.

SQL is a standard language for accessing databases. It stand for structured query language.

RDBMS stands for Relational Database Management System. 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.

SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

• SELECT - extracts data from a database 

SELECT column_name(s) FROM table_name

• UPDATE - updates data in a database: It is used to update existing records in a table.
•  
• UPDATE table_name SET column1=value, column2=value2  WHERE some_column=some_value
•  
• DELETE - deletes data from a database
•  
• The DELETE statement is used to delete records in a table.
  DELETE FROM table_name WHERE some_column=some_value 
•  The INSERT INTO Statement : It is used to insert a new row in a table.
• It is possible to write the INSERT INTO statement in two forms. The first form doesn't specify the column names where the data will be inserted, only their values:

  INSERT INTO table_name VALUES (value1, value2, value3,...)

  
  
  The second form specifies both the column names and the values to be inserted:
  INSERT INTO table_name (column1, column2, column3,...)
  VALUES (value1, value2, value3,...)

The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables. The most important DDL statements in SQL are:

• CREATE DATABASE - creates a new database : - CREATE DATABASE database_name
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• CREATE TABLE Persons:
  (P_Id int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255)
•  
• ALTER TABLE - modifies a table
• The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
  
  

  ALTER TABLE table_name ADD column_name datatype

  ALTER TABLE table_name DROP COLUMN column_name

   
• ALTER TABLE table_name
  ALTER COLUMN column_name datatype
•  
• DROP TABLE - deletes a table: :- DROP TABLE table_name
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

Distinct:

In a table, some of the columns may contain duplicate values.  The DISTINCT keyword can be used to return only distinct (different) values.

SELECT DISTINCT column_name(s)  FROM table_name;

  

WHERE Clause :

The WHERE clause is used to extract only those records that fulfill a specified criterion.
SELECT column_name(s) FROM table_name WHERE column_name operator value ;

The AND & OR Operators The AND operator displays a record if both the first condition and the second condition is true.The OR operator displays a record if either the first condition or the second condition is true.

Ex: SELECT * FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola')

 The ORDER BY Keyword The ORDER BY keyword is used to sort the result-set by a specified column. The ORDER BY keyword sort the records in ascending order by default.

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

SQL Wildcards can substitute for one or more characters when searching for data in a database.SQL wildcards must be used with the SQL LIKE operator.With SQL, the following wildcards can be used:

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[charlist]	Any single character in charlist
[^charlist] [!charlist]

SQL JOIN:

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.Tables in a database are often related to each other with keys.
Different SQL JOINs
Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

• INNER JOIN: Return rows when there is at least one match in both tables 

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table 

SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

• FULL JOIN: Return rows when there is a match in one of the tables

 SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2

PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.

The SELECT INTO statement selects data from one table and inserts it into a different table.
The SELECT INTO statement is most often used to create backup copies of tables.

SQL SELECT INTO Syntax

We can select all columns into the new table:

SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename where condition

ok