DBA’s Essential 100 SQL Commands

DBA's Essential 100 SQL Commands

All Courses

DBA Script

Materials

Library

Interviews

100 syntax what DBA should know in detail

A database administrator (DBA) should be familiar with a wide range of SQL statements and other database management tasks to efficiently manage and maintain databases. Below is a list of 100 SQL statements and concepts a DBA should know:

These syntax examples are based on standard SQL and are generally applicable across different SQL databases. However, be aware that there might be some variations or additional syntaxes specific to certain database management systems.

SELECT

 

				
					SELECT column1, column2 FROM table_name;

				
			

INSERT

				
					INSERT INTO table_name (column1, column2) VALUES (value1, value2);

				
			

UPDATE

				
					UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

				
			

DELETE

				
					DELETE FROM table_name WHERE condition;

				
			

CREATE TABLE

				
					CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

				
			

ALTER TABLE

				
					ALTER TABLE table_name ADD column_name datatype;

				
			

DROP TABLE

				
					DROP TABLE table_name;

				
			

TRUNCATE TABLE

				
					TRUNCATE TABLE table_name;

				
			

ADD COLUMN

				
					ALTER TABLE table_name ADD column_name datatype;

				
			

DROP COLUMN

This script helps identify the most expensive queries which consume the most I/O.

				
					ALTER TABLE table_name DROP COLUMN column_name;

				
			

MODIFY COLUMN

				
					ALTER TABLE table_name MODIFY COLUMN column_name datatype;

				
			

RENAME COLUMN

				
					ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

				
			

CREATE INDEX

				
					CREATE INDEX index_name ON table_name (column1, column2, ...);

				
			

DROP INDEX

				
					DROP INDEX index_name;

				
			

CREATE DATABASE

				
					CREATE DATABASE database_name;

				
			

USE

				
					USE database_name;

				
			

DROP DATABASE

				
					DROP DATABASE database_name;

				
			

GRANT

				
					GRANT permission_type ON database_name.table_name TO 'username'@'hostname';

				
			

REVOKE

				
					REVOKE permission_type ON database_name.table_name FROM 'username'@'hostname';

				
			

COMMIT

				
					COMMIT;

				
			

ROLLBACK

				
					ROLLBACK;

				
			

SAVEPOINT

				
					SAVEPOINT savepoint_name;

				
			

SET TRANSACTION

				
					SET TRANSACTION [READ WRITE | READ ONLY];

				
			

BEGIN TRANSACTION

				
					BEGIN;

				
			

WHERE

				
					SELECT column1, column2 FROM table_name WHERE condition;

				
			

ORDER BY

				
					SELECT column1, column2 FROM table_name ORDER BY column1 ASC, column2 DESC;

				
			

GROUP BY

				
					SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

				
			

HAVING

				
					SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 1;

				
			

DISTINCT

				
					SELECT DISTINCT column1 FROM table_name;

				
			

COUNT

				
					SELECT COUNT(*) FROM table_name;

				
			

AVG

				
					SELECT AVG(column_name) FROM table_name;

				
			

SUM

				
					SELECT SUM(column_name) FROM table_name;

				
			

MIN

				
					SELECT MIN(column_name) FROM table_name;

				
			

MAX

				
					SELECT MAX(column_name) FROM table_name;

				
			

LIKE

				
					SELECT column1 FROM table_name WHERE column1 LIKE 'pattern%';

				
			

IN

				
					SELECT column1 FROM table_name WHERE column1 IN (value1, value2, ...);

				
			

BETWEEN

				
					SELECT column1 FROM table_name WHERE column1 BETWEEN value1 AND value2;

				
			

NULL

				
					SELECT column1 FROM table_name WHERE column1 IS NULL;

				
			

IS NULL

				
					SELECT column1 FROM table_name WHERE column1 IS NULL;

				
			

IS NOT NULL

				
					SELECT column1 FROM table_name WHERE column1 IS NOT NULL;

				
			

UNION

				
					SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

				
			

UNION ALL

				
					SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

				
			

INTERSECT

				
					SELECT column1 FROM table1
INTERSECT
SELECT column1 FROM table2;

				
			

EXCEPT

				
					SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;

				
			

JOIN

				
					SELECT column1, column2 FROM table1
JOIN table2
ON table1.column1 = table2.column1;

				
			

INNER JOIN

				
					SELECT column1, column2 FROM table1
INNER JOIN table2
ON table1.column1 = table2.column1;

				
			

LEFT JOIN (or LEFT OUTER JOIN)

				
					SELECT column1, column2 FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column1;

				
			

RIGHT JOIN (or RIGHT OUTER JOIN):

				
					SELECT column1, column2 FROM table1
RIGHT JOIN table2
ON table1.column1 = table2.column1;

				
			

FULL JOIN (or FULL OUTER JOIN

				
					SELECT column1, column2 FROM table1
FULL JOIN table2
ON table1.column1 = table2.column1;

				
			

CROSS JOIN

				
					SELECT column1, column2 FROM table1
CROSS JOIN table2;

				
			

SELF JOIN

				
					SELECT a.column_name, b.column_name
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

				
			

CASE

				
					SELECT column1,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result3
    END
FROM table_name;

				
			

CAST

				
					SELECT CAST(column_name AS new_data_type) FROM table_name;

				
			

CONVERT

				
					SELECT CONVERT(new_data_type, column_name) FROM table_name;

				
			

COALESCE

				
					SELECT COALESCE(column1, column2, ...) FROM table_name;

				
			

NULLIF

				
					SELECT NULLIF(column1, column2) FROM table_name;

				
			

IDENTITY

				
					CREATE TABLE table_name (
    column1 datatype IDENTITY,
    column2 datatype,
    ...
);

				
			

PRIMARY KEY

				
					ALTER TABLE table_name
ADD PRIMARY KEY (column1, column2, ...);

				
			

FOREIGN KEY

				
					ALTER TABLE table_name
ADD FOREIGN KEY (column_name) REFERENCES referenced_table_name(referenced_column_name);

				
			

CHECK

				
					ALTER TABLE table_name
ADD CHECK (condition);

				
			

DEFAULT

				
					ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT value;

				
			

INDEX

				
					CREATE INDEX index_name
ON table_name (column1, column2, ...);

				
			

VIEW

				
					CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

				
			

CREATE VIEW

				
					CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

				
			

DROP VIEW

				
					DROP VIEW view_name;

				
			

SCHEMA

				
					CREATE SCHEMA schema_name;

				
			

CREATE SCHEMA

				
					CREATE SCHEMA schema_name;

				
			

TRIGGER

				
					CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
EXECUTE procedure_name;

				
			

CREATE TRIGGER

				
					CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
EXECUTE procedure_name;

				
			

DROP TRIGGER

				
					DROP TRIGGER trigger_name ON table_name;

				
			

CURSOR

				
					DECLARE cursor_name CURSOR FOR
SELECT column1, column2 FROM table_name WHERE condition;

				
			

FETCH

				
					FETCH NEXT FROM cursor_name;

				
			

STORED PROCEDURE

				
					CREATE PROCEDURE procedure_name AS
SQL_statement;
GO

				
			

CREATE PROCEDURE

				
					CREATE PROCEDURE procedure_name AS
SQL_statement;
GO

				
			

EXECUTE

				
					EXECUTE procedure_name;

				
			

FUNCTION

				
					CREATE FUNCTION function_name (@parameter datatype)
RETURNS datatype AS
BEGIN
    -- function body
    RETURN result;
END;

				
			

CREATE FUNCTION

				
					CREATE FUNCTION function_name (@parameter datatype)
RETURNS datatype AS
BEGIN
    -- function body
    RETURN result;
END;

				
			

SEQUENCE

				
					CREATE SEQUENCE sequence_name
START WITH start_value
INCREMENT BY increment_value;

				
			

CREATE SEQUENCE

				
					CREATE SEQUENCE sequence_name
START WITH start_value
INCREMENT BY increment_value;

				
			

SET

				
					SET variable_name = value;

				
			

DECLARE

				
					DECLARE @variable_name datatype;
SET @variable_name = value;

				
			

PARTITION BY

				
					SELECT column1, column2,
aggregate_function(column3) OVER (PARTITION BY column1)
FROM table_name;

				
			

RANK

				
					SELECT column1, column2,
RANK() OVER (ORDER BY column1)
FROM table_name;

				
			

DENSE_RANK

				
					SELECT column1, column2,
DENSE_RANK() OVER (ORDER BY column1)
FROM table_name;

				
			

NTILE

				
					SELECT column1, column2,
NTILE(number_of_tiles) OVER (ORDER BY column1)
FROM table_name;

				
			

LEAD

				
					SELECT column1, LEAD(column1) OVER (ORDER BY column2)
FROM table_name;

				
			

LAG

				
					SELECT column1, LAG(column1) OVER (ORDER BY column2)
FROM table_name;

				
			

FIRST_VALUE

				
					SELECT column1, FIRST_VALUE(column1) OVER (ORDER BY column2)
FROM table_name;

				
			

LAST_VALUE

				
					SELECT column1, LAST_VALUE(column1) OVER (ORDER BY column2)
FROM table_name;

				
			

CUME_DIST

				
					SELECT column1, CUME_DIST() OVER (ORDER BY column2)
FROM table_name;

				
			

PERCENT_RANK

				
					SELECT column1, PERCENT_RANK() OVER (ORDER BY column2)
FROM table_name;

				
			

ROW_NUMBER

				
					SELECT column1, ROW_NUMBER() OVER (ORDER BY column2)
FROM table_name;

				
			

EXPLAIN

				
					EXPLAIN SELECT * FROM table_name;

				
			

LIMIT

				
					SELECT column1 FROM table_name WHERE column1 BETWEEN value1 AND value2;

				
			

OFFSET

				
					SELECT * FROM table_name LIMIT 10 OFFSET 20;

				
			

FETCH FIRST

				
					SELECT * FROM table_name
FETCH FIRST 10 ROWS ONLY;

				
			

TABLESAMPLE

				
					SELECT * FROM table_name TABLESAMPLE (10 PERCENT);

				
			

CONCAT

				
					SELECT CONCAT(column1, column2) AS new_column FROM table_name;

				
			

STRING_AGG

				
					SELECT STRING_AGG(column_name, ',') FROM table_name;

				
			

DATEPART

				
					SELECT DATEPART(year, column_name) AS year_part FROM table_name;