Download SQL starting to ending information and more Cheat Sheet Programming Languages in PDF only on Docsity!
SQL
- SQL ( Structured Query Language ) is used to perform operations on the records stored in the database such as updating records, deleting records, creating and modifying tables , views, etc.
- SQL is just a query language ; it is not a database. To perform SQL queries, you need to install any database , for example, Oracle, MySQL, MongoDB, PostGre SQL, SQL Server, DB2, etc.
What is SQL?
- SQL stands for Structured Query Language.
- It is designed for managing data in a relational database management system ( RDBMS ).
- It is pronounced as S-Q-L or sometime See-Qwell.
- SQL is a database language, it is used for database creation, deletion, fetching rows, and modifying rows, etc.
All DBMS like MySQL, Oracle, MS Access, Sybase, Informix, PostgreSQL, and SQL Server use SQL as standard database language.
Why SQL is required?
SQL is required:
- To create new databases, tables and views
- To insert records in a database
- To update records in a database
- To delete records from a database
- To retrieve data from a database
What SQL does?
o With SQL, we can query our database in several ways, using English-like statements. o With SQL, a user can access data from a relational database management system. o It allows the user to describe the data. o It allows the user to define the data in the database and manipulate it when needed. o It allows the user to create and drop database and table. o It allows the user to create a view, stored procedure, function in a database. o It allows the user to set permission on tables, procedures, and views.
SQL Syntax
SQL follows some unique set of rules and guidelines called syntax. Here, we are providing all the basic SQL syntax.
o SQL is not case sensitive. Generally SQL keywords are written in uppercase. o SQL statements are dependent on text lines. We can place a single SQL statement on one or multiple text lines. o You can perform most of the action in a database with SQL statements.
SQL statement
SQL statements are started with any of the SQL commands/keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP etc. and the statement ends with a semicolon (;).
Example of SQL statement:
- SELECT "column_name" FROM "table_name";
Why semicolon is used after SQL statements:
1. Data Definition Language (DDL)
o DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc. o All the command of DDL are auto-committed that means it permanently save all the changes in the database.
Here are some commands that come under DDL:
o CREATE o ALTER o DROP o TRUNCATE
2. Data Manipulation Language
o DML commands are used to modify the database. It is responsible for all form of changes in the database. o The command of DML is not auto-committed that means it can't permanently save all the changes in the database. They can be rollback.
Here are some commands that come under DML:
o INSERT o UPDATE o DELETE
3. Data Query Language
DQL is used to fetch the data from the database.
It uses only one command:
o SELECT
Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. "Sign Up" or "Login Credentials" or “Orders” or “Employ Info”). Tables contain records (rows) with data.
Table Name- Sign Up
ID FN LN MobNo EmailId City 1 Rahul Gandhi 1111 [email protected] Pune 2 Arvind Kejriwal 2222 [email protected] Mumbai 3 Anna Hajare 3333 [email protected] Pune 4 Sharad Pawar 4444 [email protected] Baramati 5 Soniya Gandhi 5555 [email protected] Pune
The table above contains five records (one for each user) and 6 columns (ID, FN, LN, MobNo, EmailId, & City).
Example
SELECT FN , LN FROM SignUp ;
Output / Result
FN LN 1 Rahul Gandhi 2 Arvind Kejriwal 3 Anna Hajare 4 Sharad Pawar 5 Soniya Gandhi
2. DISTINCT
The SELECT DISTINCT statement is used to return / show only distinct (different) values or unique values in the particular column.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
The following SQL statement selects only the DISTINCT values from the "LN" column in the "SignUp" table:
Syntax
SELECT DISTINCT column1 , column2,... FROM table_name ;
Example
SELECT DISTINCT LN FROM SignUp ;
Output / Result
LN 1 Gandhi 2 Kejriwal 3 Hajare 4 Pawar
SELECT Example Without DISTINCT
The following SQL statement selects all (including the duplicates) values from the "LN" column in the "SignUp" table:
Number of Records-
3. TOP
It is used to specify the number of records to return.
It is a SQL statement it used to show / display the top records in a particular column.
It is a SQL statement use to select top values in a particular table
The following SQL statement selects the first three records from the "Signup" table (for SQL Server/MS Access):
Syntax
SELECT TOP value / number * FROM table_name;
Example
SELECT TOP 3 * FROM SignUp;
Output
ID FN LN MobNo EmailId City 1 1 Rahul Gandhi 1111 [email protected] Pune 2 2 Arvind Kejriwal 2222 [email protected] Mumbai 3 3 Anna Hajare 3333 [email protected] Pune
The following SQL statement selects the first 50% of the records from the "Sign Up" table (for SQL Server/MS Access):
Syntax
SELECT TOP 50 PERCENT * FROM table_name ;
Example
SELECT TOP 50 PERCENT * FROM SignUp ;
Output
ID FN LN MobNo EmailId City 1 1 Rahul Gandhi 1111 [email protected] Pune 2 2 Arvind Kejriwal 2222 [email protected] Mumbai 3 3 Anna Hajare 3333 [email protected] Pune
ORDER BY DESC Example
The following SQL statement selects all SignUp from the "SignUp" table, sorted DESCENDING by the "FN" column:
Syntax
SELECT * FROM table_name ORDER BY column1 DESC;
Example
SELECT * FROM SignUP ORDER BY FN DESC;
Output
ID FN LN MobNo EmailId City 2 5 Soniya Gandhi 5555 [email protected] Pune 1 4 Sharad Pawar 4444 [email protected] Baramati 3 1 Rahul Gandhi 1111 [email protected] Pune 4 2 Arvind Kejriwal 2222 [email protected] Mumbai 5 3 Anna Hajare 3333 [email protected] Pune
5. Where Clause
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!
The following SQL statement selects all the SignUp from the City "Pune", in the "SignUp" table:
Syntax
SELECT * FROM table_name WHERE Condition or Column Name=Value ;
Example
SELECT * FROM SignUp WHERE City='Pune' ;
Output
ID FN LN MobNo EmailId City 1 1 Rahul Gandhi 1111 [email protected] Pune 2 3 Anna Hajare 3333 [email protected] Pune 3 5 Soniya Gandhi 5555 [email protected] Pune
Types of Where Clause
- OR
- AND
The WHERE clause can be combined with AND & OR operators.
The AND and OR operators are used to filter records based on more than one condition:
The AND operator displays a record if all the conditions separated by AND are TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE.
1. OR 1 1=1 1 0 =1 0 1=1 0 0=
It is a SQL statement use to select records when both condition are true or one condition is true.
It is a SQL statement use to select records either one condition must be true then output will be true.
The following SQL statement selects all fields from "SignUp" where FN is "Rahul" OR LN is "Gandhi":
Syntax
SELECT * FROM table_name WHERE Condition1 OR Condition 2 ;
Example
SELECT * FROM SignUp WHERE FN=’Rahul’ OR LN=’Gandhi’ ; 1 1 = 1 0 1=
Output
ID FN LN Mob. No Email Id City 1 1 Rahul Gandhi 1111 [email protected] Pune 2 5 Soniya Gandhi 5555 [email protected] Pune
Syntax
SELECT Column1, Column FROM table_name WHERE Condition1 OR Condition 2 ;
Example
SELECT Email Id FROM SignUp WHERE FN=’Rahul’ OR LN=’Gandhi’ ;
Output
Email Id (^1) [email protected] (^2) [email protected]
Output
Mob. No 1 1111
6. LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
The percent sign (%) represents zero, one, or multiple characters The underscore sign (_) represents one, single character
The following SQL statement selects all SignUp with a FN starting with "A":
For Starting with Specific alphabets
Syntax
SELECT * FROM table_name WHERE column LIKE pattern ;
Example
SELECT * FROM SignUp WHERE FN LIKE “A%” ;
Output
ID FN LN Mob. No Email Id City 1 3 Anna Hajare 3333 [email protected] Pune 2 2 Arvind Kejriwal 2222 [email protected] Mumbai
Syntax
SELECT column1,column2,... FROM table_name WHERE column LIKE pattern ;
Example
SELECT LN FROM SignUp WHERE FN LIKE “A%” ;
Output
LN 1 Kejriwal 2 Hajare
For Ending with Specific alphabets
The following SQL statement selects all SignUp with a FN ending with "d":
Syntax
SELECT * FROM table_name WHERE column LIKE pattern ;
Example
SELECT * FROM SignUp WHERE FN LIKE “%d” ;
