Scribd
Upload
17 views

SQL Notes

Uploaded by

velukarthick3010
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
17 views

SQL Notes

Uploaded by

velukarthick3010
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 8

1.

Basic Commands

SELECT Statements

SELECT column1, column2, ...


FROM table_name;

● SELECT: Used to select data from a database. The data returned is stored in a result table, sometimes
called the result set.

DISTINCT: Eliminates duplicate records.

SELECT DISTINCT column1 FROM table_name;

WHERE Clause
Filters records based on a condition.

SELECT column1, column2


FROM table_name
WHERE condition;

ORDER BY Clause
Sorts the result set in ascending (ASC) or descending (DESC) order.

SELECT column1, column2


FROM table_name
ORDER BY column1 ASC | DESC;

LIMIT Clause
Limits the number of records returned.

SELECT column1, column2


FROM table_name
LIMIT number;

●
AND, OR, and NOT Operators
Used with WHERE to filter records based on more than one condition.

SELECT * FROM table_name


WHERE condition1 AND condition2;

SELECT * FROM table_name


WHERE condition1 OR condition2;

SELECT * FROM table_name


WHERE NOT condition;

2. Aggregate Functions
COUNT(): Returns the number of rows.

SELECT COUNT(column_name) FROM table_name;

SUM(): Adds up values in a numeric column.

SELECT SUM(column_name) FROM table_name;

AVG(): Returns the average value of a numeric column.

SELECT AVG(column_name) FROM table_name;

MIN() and MAX(): Return the smallest and largest value.

SELECT MIN(column_name) FROM table_name;


SELECT MAX(column_name) FROM table_name;

●
3. Joins

INNER JOIN
Returns records with matching values in both tables.

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

LEFT JOIN (LEFT OUTER JOIN)


Returns all records from the left table, and the matched records from the right table.

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

RIGHT JOIN (RIGHT OUTER JOIN)


Returns all records from the right table, and the matched records from the left table.

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

FULL JOIN (FULL OUTER JOIN)


Returns all records when there is a match in either table.

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

●
4. Aliases
Used to rename a table or column temporarily.

SELECT column1 AS alias_name


FROM table_name AS alias_table;

5. Data Manipulation Language (DML)

INSERT INTO
Inserts new data into a table.

INSERT INTO table_name (column1, column2, ...)


VALUES (value1, value2, ...);

UPDATE
Modifies existing records.

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

DELETE
Deletes records.

DELETE FROM table_name


WHERE condition;

6. Data Definition Language (DDL)

CREATE TABLE
Creates a new table.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);

ALTER TABLE
Modifies a table’s structure.

ALTER TABLE table_name


ADD column_name datatype;

ALTER TABLE table_name


DROP COLUMN column_name;

ALTER TABLE table_name


MODIFY COLUMN column_name datatype;

DROP TABLE
Deletes a table and all its data.

DROP TABLE table_name;

TRUNCATE TABLE
Deletes all records but keeps the table structure.

TRUNCATE TABLE table_name;

7. Constraints
PRIMARY KEY: Uniquely identifies each record.

CREATE TABLE table_name (


column1 datatype PRIMARY KEY,
column2 datatype
);

FOREIGN KEY: Links two tables.

CREATE TABLE table_name (


column1 datatype,
column2 datatype,
FOREIGN KEY (column2) REFERENCES other_table(column_name)
);

UNIQUE: Ensures all values in a column are unique.

CREATE TABLE table_name (


column1 datatype UNIQUE,
column2 datatype
);

NOT NULL: Ensures that a column cannot have a NULL value.

CREATE TABLE table_name (


column1 datatype NOT NULL,
column2 datatype
);

CHECK: Ensures that all values satisfy a condition.

CREATE TABLE table_name (


column1 datatype CHECK (column1 condition),
column2 datatype
);

DEFAULT: Sets a default value for a column.


CREATE TABLE table_name (
column1 datatype DEFAULT value,
column2 datatype
);

8. Subqueries
A query within a query, often nested in WHERE or FROM clauses.

SELECT column1
FROM table_name
WHERE column1 IN (SELECT column1 FROM table_name WHERE condition);

9. CASE Statements
Allows conditional logic in .

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

10. Views
A virtual table based on the result-set of a query.

CREATE VIEW view_name AS


SELECT column1, column2
FROM table_name
WHERE condition;
●

Modifying Views

CREATE OR REPLACE VIEW view_name AS


SELECT columns
FROM table_name
WHERE condition;

You might also like