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;