All SQL Commands | Structured Query Language | DDL, DML, DCL, TCL, DQL

0

 

All SQL Commands


SQL(caps)Structured Query Language (SQL) is a special-purpose programming language designed for managing data held in a Relational Database Management System (RDBMS). SQL-like languages can also be used in Relational Data Stream Management Systems (RDSMS), or in "not-only SQL" (NoSQL) databases.

SQL comprises of 3 major sub-languages:

  1. Data Definition Language (DDL): to create and modify the structure of the database;
  2.  Data Manipulation Language (DML): to perform Read, Insert, Update and Delete operations on the data of the database;
  3. Data Control Language (DCL): to control the access of the data stored in the database.


The core DML operations are Create, Read, Update and Delete (CRUD for short) which are performed by the statements INSERT, SELECT, UPDATE and DELETE. There is also a (recently added) MERGE statement which can perform all 3 write operations (INSERT, UPDATE, DELETE).

Many SQL databases are implemented as client/server systems; the term "SQL server" describes such a database. At the same time, Microsoft makes a database that is named "SQL Server". While that database speaks a dialect of SQL, information specific to that database is not on topic in this tag but belongs into the SQL Server documentation.


DQL(Data Query Language)- To fetch the data from the database
Example: SELECT

DML(Data Manipulation Language)- To modify the database objects
Example: INSERT,UPDATE,DELETE

DDL(Data Definition Language)- To create & modify database objects
Example: CREATE,DROP,ALTER,TRUNCATE

1] FUNDAMENTALS OF SQL

I) CREATE

CREATE statement is used to create a table
Syntax:
CREATE TABLE "TABLE_NAME"(
  "COLUMN1" "DATA_TYPE" CONSTRAINTS,
  "COLUMN2" "DATA_TYPE" CONSTRAINTS,
  "COLUMN3" "DATA_TYPE" CONSTRAINTS,
	..............................
  "COLUMN N" "DATA_TYPE" CONSTRAINTS);

II) INSERT

INSERT statement is used insert new data into the table
Syntax:
INSERT INTO "TABLE_NAME" (COL1, COL2, ……..COL_N)
VALUES (Col_val_1,Col_val_2, ……. COL_N);
        
  • Import data from file(PostgreSQL)
For csv file
COPY TABLE_NAME(column1, column2,… ) FROM
'FILE_PATH' DELIMITER ',' CSV HEADER;
        
For txt file
COPY TABLE_NAME(column1, column2,… ) FROM
'FILE_PATH' DELIMITER ',';
        

III) SELECT

SELECT statement is used to retrieve data from the table
Syntax:
SELECT * FROM "TABLE_NAME";
  • For select one column
Syntax:
SELECT "COLUMN_NAME" FROM "TABLE_NAME";
  • For select multiple columns
Syntax:
SELECT "COLUMN1, COLUMN2, …" FROM "TABLE_NAME";
  • For select all columns
Syntax:
SELECT * FROM "TABLE_NAME";

IV) DISTINCT 

DISTINCT keyword is used to eliminate all duplicate records & fetch only unique records 
Syntax:
Syntax:
SELECT DISTINCT(*) FROM "TABLE_NAME";

V) WHERE 

WHERE clause is used to filter records 
Syntax:
Syntax:
SELECT "COLUMN_NAME(S)"
        FROM "TABLE_NAME"
        WHERE CONDITION;

VI) AND/OR 

The AND/OR is used to combine multiple conditions  
Syntax:
Syntax:
SELECT "COLUMN_NAME(S)"
        FROM "TABLE_NAME"
        WHERE CONDITION AND/OR CONDITION;

VII) UPDATE 

The UPDATE is used to modify the existing data in the table 
Syntax::
Syntax:
UPDATE "TABLE_NAME"
        SET COL_1 = VAL_1, COL_2 = VAL_2, …
        WHERE CONDITION;

VIII) DELETE 

The DELETE is used to delete existing records in the table 
Syntax: 
  • For delete all rows:
Syntax:
DELETE FROM "TABLE_NAME";
  • For delete single/multiple row(s):
Syntax:
DELETE FROM "TABLE_NAME"
        WHERE CONDITION;

IX) ALTER 

The ALTER is used to change the definition or structure of the table 
Syntax: 
  • ADD COLUMN:
Syntax:
ALTER TABLE "TABLE_NAME"
        ADD "COLUMN_NAME" "DATA_TYPE";
  • DROP COLUMN:
Syntax:
ALTER TABLE "TABLE_NAME"
        DROP "COLUMN_NAME";
  • MODIFY DATA TYPE:
Syntax:
ALTER TABLE "TABLE_NAME"
        ALTER COLUMN "COL_NAME" TYPE NEW_DATA_TYPE;
  • RENAME COLUMN:
Syntax:
ALTER TABLE "TABLE_NAME"
        RENAME COLUMN "COL_NAME" TO "NEW_NAME";
  • ADD CONSTRAINTS:
Syntax:
ALTER TABLE "TABLE_NAME"
        ADD CONSTRAINT COL_NAME CHECK CONDITION;

2] FILTERING COMMANDS

I) IN

The IN Used to reduce multiple OR logical operators in SELECT, DELETE, INSERT, and UPDATE statements.
Syntax:
SELECT COL_NAME FROM TABLE_NAME
    WHERE COL_NAME IN ('VAL1', 'VAL2', ...);

II) BETWEEN

The BETWEEN Used to retrieve data within a given range.
Syntax:
SELECT COL_NAME(S) FROM TABLE_NAME
          WHERE COL_NAME BETWEEN 'VAL1' AND 'VAL2';

III) LIKE

The LIKE Used to perform pattern matching/regex using wildcards ("%", "_").
  • "%" - matches any string of any length.
  • "_" - matches a single character.
Syntax:
SELECT COL_NAME FROM TABLE_NAME
            WHERE COL_NAME LIKE 'PATTERN';

3] ORDERING COMMANDS

I) ORDER BY

The ORDER BY Used to sort the data and is only used in the SELECT statement.
Syntax:
SELECT COL_NAME(s) FROM TABLE_NAME
      ORDER BY COL_NAME ASC/DESC;

II) LIMIT

The LIMIT Used to limit the number of records based on a given limit.
Syntax:
SELECT COL_NAME(S) FROM TABLE_NAME
      [WHERE & ORDER BY – Optional]
      LIMIT LIMIT_VALUE;

4] ALIAS

I) AS

The AS Used to assign an alias to the column.
Syntax:
SELECT COL_NAME as COL_ALIAS
        FROM TABLE_NAME;

5] AGGREGATE COMMANDS

I) COUNT()

The COUNT() Used to count the expression.
Syntax:
SELECT COUNT(COL_NAME) FROM TABLE_NAME;

II) SUM()

The SUM() Used to sum the expression.
Syntax:
SELECT SUM(COL_NAME) FROM TABLE_NAME;

III) MIN()

The MIN() returns the minimum value of a column.
Syntax:
SELECT MIN(COL_NAME) FROM TABLE_NAME;

IV) MAX()

The MAX() returns the maximum value of a column.
Syntax:
SELECT MAX(COL_NAME) FROM TABLE_NAME;

V) AVG()

The AVG() Calculates the average value of a numeric column.
Syntax:
SELECT AVG(COL_NAME) FROM TABLE_NAME;

6] GROUP BY COMMANDS

The GROUP BY COMMANDS Used to group rows that have the same values in specified columns into summary rows.
Syntax:
SELECT COL_NAME, COUNT(*)
      FROM TABLE_NAME
      GROUP BY COL_NAME;
Example:
SELECT department, COUNT(*)
      FROM employees
      GROUP BY department;

7] CONDITIONAL STATEMENT

I) CASE

The CASE Used to perform conditional logic in SQL queries.
Syntax:
SELECT COL_NAME,
      CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result3
      END AS alias_name
    FROM TABLE_NAME;
Example:
SELECT name,
      CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        ELSE 'C'
      END AS grade
    FROM students;

8] JOINS

I) INNER JOIN

The INNER JOIN Selects records that have matching values in both tables.
Syntax:
SELECT columns
        FROM table1
        INNER JOIN table2 ON table1.column = table2.column;
Example:
SELECT employees.name, departments.department_name
        FROM employees
        INNER JOIN departments ON employees.department_id = departments.department_id;

II) LEFT JOIN

The LEFT JOIN Selects all records from the left table, and the matched records from the right table.
Syntax:
SELECT columns
          FROM table1
          LEFT JOIN table2 ON table1.column = table2.column;
Example:
SELECT employees.name, departments.department_name
          FROM employees
          LEFT JOIN departments ON employees.department_id = departments.department_id;

III) RIGHT JOIN

The RIGHT JOIN Selects all records from the right table, and the matched records from the left table.
Syntax:
SELECT columns
            FROM table1
            RIGHT JOIN table2 ON table1.column = table2.column;
Example:
SELECT employees.name, departments.department_name
            FROM employees
            RIGHT JOIN departments ON employees.department_id = departments.department_id;

IV) FULL JOIN

The FULL JOIN Selects all records when there is a match in either left or right table
Syntax:
SELECT columns
            FROM table1
            FULL JOIN table2 ON table1.column = table2.column;
Example:
SELECT employees.name, departments.department_name
            FROM employees
            FULL JOIN departments ON employees.department_id = departments.department_id;

9] SUBQUERY

I) Subquery in SELECT

A SUBQUERY is a query within another query.
Syntax:
SELECT column1,
        (SELECT column2 FROM table2 WHERE table2.column3 = table1.column3) AS alias_name
      FROM table1;
Example:
SELECT name,
        (SELECT AVG(score) FROM exams WHERE exams.student_id = students.id) AS average_score
      FROM students;

10] VIEW & INDEX

I) VIEW

The VIEW virtual table based on the result set of a SELECT statement.
Syntax:
CREATE VIEW view_name AS
            SELECT columns
            FROM table
            WHERE condition;
Example:
CREATE VIEW high_salary_employees AS
            SELECT name, salary
            FROM employees
            WHERE salary > 50000;

II) INDEX

The INDEX A database object that improves the speed of data retrieval operations.
Syntax:
CREATE INDEX index_name
                ON table_name (column1, column2, ...);
Example:
CREATE INDEX idx_employee_name
                ON employees (name);

11] STRING FUNCTIONS

I) CONCAT

The CONCAT Combines two or more strings into one string.
Syntax:
SELECT CONCAT(string1, string2, ...) AS new_string
                FROM table_name;
Example:
SELECT CONCAT(first_name, ' ', last_name) AS full_name
                FROM employees;

II) SUBSTRING

The SUBSTRING Extracts a substring from a string.
Syntax:
SELECT SUBSTRING(column_name, start_position, length) AS substring
        FROM table_name;
Example:
SELECT SUBSTRING(name, 1, 3) AS short_name
        FROM employees;

12] Mathematical Functions

I) ROUND

The ROUND a number to a specified number of decimal places.
Syntax:
SELECT ROUND(column_name, decimal_places) AS rounded_number
        FROM table_name;
Example:
SELECT ROUND(salary, 2) AS rounded_salary
        FROM employees;

II) ABS

The ABS returns the absolute value of a number.
Syntax:
SELECT ABS(column_name) AS absolute_value
        FROM table_name;
Example:
SELECT ABS(profit_loss) AS absolute_profit_loss
        FROM financials;

13] DATE-TIME FUNCTIONS

I) CURRENT_DATE

The CURRENT_DATE returns the current date.
Syntax:
SELECT CURRENT_DATE AS today_date;
Example:
SELECT CURRENT_DATE AS today_date;

II) DATE_ADD

The DATE_ADD a specified time interval to a date.
Syntax:
SELECT DATE_ADD(date_column, INTERVAL value unit) AS new_date
            FROM table_name;
Example:
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) AS next_anniversary
            FROM employees;

14] PATTERN MATCHING

I) REGEXP

The REGEXP Performs regular expression pattern matching.
Syntax:
SELECT column_name
            FROM table_name
            WHERE column_name REGEXP 'pattern';
Example:
SELECT name
            FROM employees
            WHERE name REGEXP '^[A-Z]';

15] DATA TYPE CONVERSION FUNCTIONS

I) CAST

The CAST Converts a value from one data type to another.
Syntax:
SELECT CAST(column_name AS new_data_type) AS new_column_name
                FROM table_name;
Example:
SELECT CAST(salary AS DECIMAL(10, 2)) AS salary_decimal
                FROM employees;

II) CONVERT

The CONVERT a value from one data type to another.
Syntax:
SELECT CONVERT(column_name, new_data_type) AS new_column_name
                    FROM table_name;
Example:
SELECT CONVERT(salary, DECIMAL(10, 2)) AS salary_decimal
                    FROM employees;

Post a Comment

0Comments

Post a Comment (0)

#buttons=(Accept !) #days=(20)

Our website uses cookies to enhance your experience. Check Now
Accept !