SQL- Structured Query Language
SQL PRIMARY KEY :
SQL FOREIGN Key:
SQL Commands:
The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature:
- SQL is an ANSI (American National Standards Institute) standard
SQL PRIMARY KEY :
- The PRIMARY KEY constraint uniquely identifies each record in a database table.
- Primary keys must contain UNIQUE values.
- A primary key column cannot contain NULL values.
- Most tables should have a primary key, and each table can have only ONE primary key.
SQL FOREIGN Key:
- A foreign key is a key used to link two tables together. This is sometimes called a referencing key.
- Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table
- The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table
SQL Commands:
The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature:
DDL - Data Definition Language:
Command Description
CREATE Creates a new table, a view of a table, or other object in database
ALTER Modifies an existing database object, such as a table.
DROP Deletes an entire table, a view of a table or other object in the database.
DML - Data Manipulation Language:
Command Description
SELECT Retrieves certain records from one or more tables
INSERT Creates a record
UPDATE Modifies records
DELETE Deletes records
DCL - Data Control Language:
Command Description
GRANT Gives a privilege to user
REVOKE Takes back privileges granted from user
Command Description
CREATE Creates a new table, a view of a table, or other object in database
ALTER Modifies an existing database object, such as a table.
DROP Deletes an entire table, a view of a table or other object in the database.
DML - Data Manipulation Language:
Command Description
SELECT Retrieves certain records from one or more tables
INSERT Creates a record
UPDATE Modifies records
DELETE Deletes records
DCL - Data Control Language:
Command Description
GRANT Gives a privilege to user
REVOKE Takes back privileges granted from user
SQL has many built-in functions for performing processing on string or numeric data.
Following is the list of all useful SQL built-in functions:
1) SQL COUNT Function - The SQL COUNT aggregate function is used to count the number of rows in a database table.
2) SQL MAX Function - The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.
3) SQL MIN Function - The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
4) SQL AVG Function - The SQL AVG aggregate function selects the average value for certain table column.
5) SQL SUM Function - The SQL SUM aggregate function allows selecting the total for a numeric column.
6) SQL SQRT Functions - This is used to generate a square root of a given number.
7) SQL RAND Function - This is used to generate a random number using SQL command.
8) SQL CONCAT Function - This is used to concatenate any string inside any SQL command.
9) SQL Numeric Functions - Complete list of SQL functions required to manipulate numbers in SQL.
10) SQL String Functions - Complete list of SQL functions required to manipulate strings in SQL.
What Can SQL do?
Following is the list of all useful SQL built-in functions:
1) SQL COUNT Function - The SQL COUNT aggregate function is used to count the number of rows in a database table.
2) SQL MAX Function - The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.
3) SQL MIN Function - The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
4) SQL AVG Function - The SQL AVG aggregate function selects the average value for certain table column.
5) SQL SUM Function - The SQL SUM aggregate function allows selecting the total for a numeric column.
6) SQL SQRT Functions - This is used to generate a square root of a given number.
7) SQL RAND Function - This is used to generate a random number using SQL command.
8) SQL CONCAT Function - This is used to concatenate any string inside any SQL command.
9) SQL Numeric Functions - Complete list of SQL functions required to manipulate numbers in SQL.
10) SQL String Functions - Complete list of SQL functions required to manipulate strings in SQL.
What Can SQL do?
- SQL can execute queries against a database
- SQL can retrieve data from a database
- SQL can insert records in a database
- SQL can 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
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),PRIMARY KEY (ID)
Select ID, Name, DOB, Age
From table_Name
Where conditions
Order by - Asc/Des
Logical Operator- And, Or, Not ,True, false
Arithmetic- +, -, *, /
Comparison- >, <, in like, between
Group by and Having clause works with Aggregate function
First Name- Char/Varchar – Single quotes required ‘’
Age – Int Single quotes not required
First name Start with S – like ‘s%’ ‘_a%’
MySQL for Excel
Alignment
Text- Left
Number.- Right
Time – No Space 7am
Date- mm/dd/yyyy Right
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),PRIMARY KEY (ID)
Select ID, Name, DOB, Age
From table_Name
Where conditions
Order by - Asc/Des
Logical Operator- And, Or, Not ,True, false
Arithmetic- +, -, *, /
Comparison- >, <, in like, between
Group by and Having clause works with Aggregate function
First Name- Char/Varchar – Single quotes required ‘’
Age – Int Single quotes not required
First name Start with S – like ‘s%’ ‘_a%’
MySQL for Excel
Alignment
Text- Left
Number.- Right
Time – No Space 7am
Date- mm/dd/yyyy Right