Querying Data from a Table Modifying Data Table Management

SQL Basics

Made by: CodingHome.

Published by: AT Products LLC.

Querying Data from a Table

Query Data in Columns c1, c2 from a Table

SQL

SELECT c1, c2 FROM t;

Query All Rows and Columns from a Table

SQL

SELECT * FROM t;

Query Data and Filter Rows with a Condition

SQL

SELECT c1, c2 FROM t WHERE condition;

Query Distinct Rows from a Table

SQL

SELECT DISTINCT c1 FROM t WHERE condition;

Sort the Result Set in Ascending or Descending Order

SQL

SELECT c1, c2 FROM t
ORDER BY c1 ASC [DESC];

Skip Offset of Rows and Return the Next n Rows

SQL

SELECT c1, c2 FROM t ORDER BY c1 LIMIT n OFFSET offset;

Group Rows using an Aggregate Function

SQL

SELECT c1, aggregate(c2) FROM t GROUP BY c1;

Filter Groups using Having Clause

SQL

SELECT c1, aggregate(c2) FROM t GROUP BY c1 HAVING condition;

Modifying Data

Insert One Row into a Table

SQL

INSERT INTO t(column_list) VALUES(value_list);

Insert Multiple Rows into a Table

SQL

INSERT INTO t(column_list)
VALUES (value_list), (value_list), …;

Insert Rows from t2 into t1

SQL

INSERT INTO t1(column_list) SELECT column_list FROM t2;

Update New Value in the Column c1 for All Rows

SQL

UPDATE t SET c1 = new_value;

Update Values in the Column c1, c2 that Match the Condition

SQL

UPDATE t SET c1 = new_value, c2 = new_value WHERE condition;

Delete All Data in a Table

SQL

DELETE FROM t;

Delete Subset of Rows in a Table

SQL

DELETE FROM t WHERE condition;

Table Management

Create a new Table with Three Columns

SQL

CREATE TABLE t ( id INT PRIMARY KEY, name VARCHAR NOT NULL, price INT DEFAULT 0 );

Delete the Table from the Database

SQL

DROP TABLE t ;

Add a new Column to the Table

SQL

ALTER TABLE t ADD column;

Drop Column c from the Table

SQL

ALTER TABLE t DROP COLUMN c ;

Add a Constraint

SQL

ALTER TABLE t ADD constraint;

Drop a Constraint

SQL

ALTER TABLE t DROP constraint;

Rename a Table from t1 to t2

SQL

ALTER TABLE t1 RENAME TO t2;

Rename Column c1 to c2

SQL

ALTER TABLE t1 RENAME c1 TO c2 ;

Remove all Data in a Table

SQL

TRUNCATE TABLE t;