Download Structures Query Language and more Cheat Sheet Database Management Systems (DBMS) in PDF only on Docsity!
1. CREATING A DATABASE
In SQL, the 'Create Database' statement is a first step for storing the structured data in the database.
SYNTAX
CREATE DATABASE Database_Name;
EXAMPLE
CREATE DATABASE CLASS;
OUTPUT
2. CREATE A TABLE
SQL CREATE TABLE statement is used to create table in a database.
SYNTAX
CREATE TABLE TABLE_NAME (
COLUMN1 DATATYPE,
COLUMN2 DATATYPE,
COLUMN3 DATATYPE,
EXAMPLE
CREATE TABLE STUDENT(
ID INT PRIMARY KEY,
NAME VARCHAR (20),
ADDRESS VARCHAR (30),
AGE INT,
PINCODE INT,
MARKS INT,
DEPARTMENT VARCHAR (25)
OUTPUT
SELECT * FROM STUDENT;
OUTPUT
4. SELECT ROW FROM TABLE
The SELECT statement is used to select data from a database.
SYNTAX
SELECT column1, column2, ... FROM table_name;
EXAMPLE
SELECT * FROM STUDENT;
OUTPUT
5. WHERE CLAUSE
The WHERE clause is used to filter records. It is a data manipulation language statement.
SYNTAX
SELECT column1, column2, ... FROM table_name WHERE condition;
EXAMPLE
SELECT NAME FROM STUDENT WHERE DEPARTMENT='MCA';
OUTPUT
6. (a) WHERE CLAUSE USING AND / OR
SYNTAX
SELECT COLUMN1, COLUMN2,…
FROM TABLE_NAME
WHERE
CONDITION1 AND CONDITION2…..;
EXAMPLE
SELECT ID, NAME, ADDRESS FROM STUDENT WHERE ID=103 AND
NAME='SMAYLA';
OUTPUT
WHERE column LIKE pattern;
EXAMPLE
SELECT * FROM STUDENT WHERE NAME LIKE 'A%';
OUTPUT
EXAMPLE
SELECT * FROM STUDENT WHERE NAME LIKE '%A';
OUTPUT
9. ORDER BY
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
SYNTAX
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
EXAMPLE:- USING ASC COMMAND
SELECT * FROM STUDENT ORDER BY ID ASC;
OUTPUT
EXAMPLE:- USING DESC COMMAND
SELECT * FROM STUDENT ORDER BY ID DESC;
OUTPUT
EXAMPLE:
SELECT * FROM STUDENT WHERE NOT AGE BETWEEN 21 AND 23;
OUTPUT
11. IN/ NOT IN OPERATOR
IN OPERATOR
The IN operator allows you to specify multiple values in a WHERE clause.
SYNTAX
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
EXAMPLE
SELECT NAME FROM STUDENT WHERE ADDRESS
IN('JAMMU','UDHAMPUR');
OUTPUT
NOT IN OPERATOR
SYNTAX
SELECT COLUMN1, COLUMN2…
FROM TABLE_NAME
WHERE NOT COLUMN_NAME IN (VALUE1, VALUE2…..);
EXAMPLE
SELECT NAME FROM STUDENT WHERE ADDRESS NOT
IN('JAMMU','UDHAMPUR');
OUTPUT
13. UPDATE
The UPDATE command is used to update existing rows in a table.
SYNTAX
UPDATE TABLE_NAME SET COLUMN1=VALUE1,
COLUMN2=VALUE2…….
WHERE CONDITION;
EXAMPLE:
UPDATE STUDENT SET NAME='ANJALI' WHERE ID=102;
OUTPUT
SELECT * FROM STUDENT;
OUTPUT
14. THE SQL COUNT (), AVG () AND SUM () FUNCTIONS
COUNT ()
The COUNT () function returns the number of rows that matches a specified criterion.
SYNTAX
SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME;
EXAMPLE
SELECT COUNT (ID) FROM STUDENT;
OUTPUT
SUM ()
The SUM () function returns the total sum of a numeric column.
SYNTAX
SELECT SUM(COLUMN_NAME) FROM TABLE_NAME;
15. THE SQL MIN () AND MAX () FUNCTIONS
MIN ()
The MIN () function returns the smallest value of the selected column.
SYNTAX
SELECT MIN(COLUMN_NAME) FROM TABLE_NAME;
EXAMPLE
SELECT MIN(ID) FROM STUDENT;
OUTPUT
MAX ()
The MAX () function returns the largest value of the selected column.
SYNTAX
SELECT MAX(COLUMN_NAME) FROM TABLE_NAME;
EXAMPLE
SELECT MAX(ID) FROM STUDENT;
OUTPUT
16. GROUP BY
The GROUP BY statement is often used with aggregate functions (COUNT (), MAX (), MIN (), SUM (), AVG ()) to group the result-set by one or more columns.
SYNTAX
SELECT COLUMN1, SUM(COLUMN_NAME) FROM TABLE_NAME
GROUP BY COLUMN1;
EXAMPLE
SELECT ADDRESS, SUM(ID) FROM STUDENT GROUP BY
ADDRESS;
OUTPUT
18. CONSTRAINTS (PRIMARY KEY, NOT NULL, UNIQUE,
DEFAULT, CHECK&FOREIGN KEY)
NOT NULL
The NOT NULL constraint enforces a column to not accept NULL values, which means that you cannot insert or update a record without adding a value to this field.
SYNTAX FOR NOT NULL:
CREATE TABLE TABLE_NAME(
COL_1 DATA TYPE NOT NULL,
COL_2 DATA TYPE,…,COL_N DATA TYPE);
DEFAULT
The DEFAULT constraint provides a default value for a column.
SYNTAX FOR DEFAULT:
CREATE TABLE TABLE_NAME(
COL_1 DATA TYPE,
COL_2 DATA TYPE DEFAULT: VALUE,….., COL_N DATA TYPE);
UNIQUE
The UNIQUE constraint ensures that all values in a column are unique.
SYNTAX FOR UNIQUE:
CREATE TABLE TABLE_NAME(
COL_1 DATA TYPE UNIQUE,
COL_2 DATA TYPE,…, COL_N DATA TYPE);
PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a table.A table can have only one primary key, which may consist of one single or of multiple fields.
SYNTAX FOR PRIMARY KEY:
CREATE TABLE TABLE_NAME(
COL_1 DATA TYPE PRIMARY KEY,
COL_2 DATA TYPE,…, COL_N DATA TYPE);
CHECK
The CHECK constraint limits the value that can be placed in a column.
SYNTAX FOR CHECK:
CREATE TABLE TABLE_NAME(
COL_1 DATA TYPE CHECK(CONDITION),
COL_2 DATA TYPE,…, COL_N DATA TYPE);
