Scribd
Upload
7 views4 pages

Essential SQL Commands and Functions

The document outlines various SQL commands, including Data Definition Language (DDL) for creating, altering, and dropping tables, and Data Manipulation Language (DML) for inserting, updating, deleting, and selecting records. It also covers clauses and operators for filtering and sorting data, different types of joins, aggregate functions, and constraints for ensuring data integrity. Examples are provided for each command and concept to illustrate their usage.

Uploaded by

blissssilb8
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
7 views4 pages

Essential SQL Commands and Functions

The document outlines various SQL commands, including Data Definition Language (DDL) for creating, altering, and dropping tables, and Data Manipulation Language (DML) for inserting, updating, deleting, and selecting records. It also covers clauses and operators for filtering and sorting data, different types of joins, aggregate functions, and constraints for ensuring data integrity. Examples are provided for each command and concept to illustrate their usage.

Uploaded by

blissssilb8
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

SQL Commands, Clauses, Operators, Joins, Functions, and Constraints

1. DDL (Data Definition Language)

a. CREATE - Used to create a new table.

Syntax:

CREATE TABLE table_name (

column1 datatype constraints,

column2 datatype constraints,

...

);

Example:

CREATE TABLE students (

roll_no INT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

age INT,

city VARCHAR(50)

);

b. ALTER - Used to add, modify, or delete columns in a table.

Example (Add a column):

ALTER TABLE students ADD email VARCHAR(100);

c. DROP - Used to delete an entire table.

Example:

DROP TABLE students;

2. DML (Data Manipulation Language)

a. INSERT - Adds new records.

Example:

INSERT INTO students (roll_no, name, age, city) VALUES (1, 'Rahul', 18, 'Delhi');
SQL Commands, Clauses, Operators, Joins, Functions, and Constraints

b. UPDATE - Modifies existing records.

Example:

UPDATE students SET city = 'Mumbai' WHERE roll_no = 1;

c. DELETE - Removes records.

Example:

DELETE FROM students WHERE roll_no = 1;

d. SELECT - Retrieves data.

Example:

SELECT * FROM students;

3. Clauses and Operators

a. WHERE - Filters rows.

SELECT * FROM students WHERE age > 18;

b. GROUP BY - Groups rows by a column.

SELECT city, COUNT(*) FROM students GROUP BY city;

c. ORDER BY - Sorts rows.

SELECT * FROM students ORDER BY age DESC;

d. BETWEEN - Checks range.

SELECT * FROM students WHERE age BETWEEN 18 AND 25;

e. IN - Matches any in a list.

SELECT * FROM students WHERE city IN ('Delhi', 'Mumbai');

f. LIKE - Pattern matching.

SELECT * FROM students WHERE name LIKE 'R%';


SQL Commands, Clauses, Operators, Joins, Functions, and Constraints

g. Relational Operators - >, <, =, >=, <=, <>

SELECT * FROM students WHERE age >= 20;

h. Logical Operators - AND, OR, NOT

SELECT * FROM students WHERE age > 18 AND city = 'Delhi';

4. Joins

a. Cartesian Join - Combines all rows.

SELECT * FROM students, courses;

b. Equi Join - Joins based on a column.

SELECT * FROM students s JOIN courses c ON s.course_id = c.course_id;

c. Natural Join - Auto joins on common column names.

SELECT * FROM students NATURAL JOIN courses;

5. Aggregate Functions

a. AVG - Average value.

SELECT AVG(age) FROM students;

b. SUM - Total value.

SELECT SUM(age) FROM students;

c. MAX - Highest value.

SELECT MAX(age) FROM students;

d. MIN - Lowest value.

SELECT MIN(age) FROM students;

e. COUNT - Number of non-null entries.


SQL Commands, Clauses, Operators, Joins, Functions, and Constraints

SELECT COUNT(roll_no) FROM students;

f. COUNT(*) - Total number of rows.

SELECT COUNT(*) FROM students;

6. Other Keywords

a. DISTINCT - Removes duplicates.

SELECT DISTINCT city FROM students;

b. IS NULL - Finds missing values.

SELECT * FROM students WHERE email IS NULL;

7. Constraints

a. PRIMARY KEY - Unique + NOT NULL.

Example:

roll_no INT PRIMARY KEY

b. NOT NULL - Disallows NULL.

Example:

name VARCHAR(50) NOT NULL

c. UNIQUE - Ensures all values are different.

Example:

email VARCHAR(100) UNIQUE

d. DEFAULT - Sets a default value.

Example:

age INT DEFAULT 18

You might also like