Basic SQL Commands with Examples for

Command-Line Interface (CMD)

Author : AI PDF GPT Date : 2024-09-21

Introduction

This document provides an overview of basic SQL commands that can be executed in a
command-line interface (CMD) using popular databases such as MySQL, PostgreSQL, or
SQLite. Each section includes SQL command syntax followed by an example to help
you understand how to use the commands effectively.

Database Commands

1. Create a Database: sql CREATE DATABASE dbname; Example: CREATE

DATABASE school;

2. Select a Database: sql USE dbname; Example: USE school;

3. Delete a Database: sql DROP DATABASE dbname; Example: DROP DATABASE

school;

Table Commands

1. Create a Table: sql CREATE TABLE tablename ( column1 datatype,

column2 datatype, ... ); Example: CREATE TABLE students (id INT
PRIMARY KEY, name VARCHAR(100), age INT, grade CHAR(2));

2. Show Tables: sql SHOW TABLES; Example: SHOW TABLES;

3. Describe Table: sql DESCRIBE tablename; Example: DESCRIBE students;

4. Drop a Table: sql DROP TABLE tablename; Example: DROP TABLE students;

Alter Table Commands

1. Add a column: sql ALTER TABLE tablename ADD columnname datatype;

Example: ALTER TABLE students ADD address VARCHAR(255);

2. Drop a column: sql ALTER TABLE tablename DROP columnname; Example:

ALTER TABLE students DROP address;
 3. Modify a column: sql ALTER TABLE tablename MODIFY columnname
datatype; Example: ALTER TABLE students MODIFY age INT(3);

Data Manipulation Commands

1. Insert Data into Table: sql INSERT INTO tablename (column1, column2,
...) VALUES (value1, value2, ...); Example: INSERT INTO students
(id, name, age, grade) VALUES (1, 'John Doe', 18, 'A');

2. Select Data from Table: sql SELECT column1, column2, ... FROM
tablename; Example: SELECT name, age FROM students;

3. Update Data in Table: sql UPDATE tablename SET column1 = value1 WHERE
condition; Example: UPDATE students SET age = 19 WHERE id = 1;

4. Delete Data from Table: sql DELETE FROM tablename WHERE condition;
Example: DELETE FROM students WHERE id = 1;

Join Commands

1. Inner Join: sql SELECT columns FROM table1 INNER JOIN table2 ON
table1.column = table2.column; Example: SELECT students.name,
classes.subject FROM students INNER JOIN classes ON students.id =
classes.student_id;

2. Left Join: sql SELECT columns FROM table1 LEFT JOIN table2 ON
table1.column = table2.column; Example: SELECT students.name,
classes.subject FROM students LEFT JOIN classes ON students.id =
classes.student_id;

3. Right Join: sql SELECT columns FROM table1 RIGHT JOIN table2 ON
table1.column = table2.column; Example: SELECT students.name,
classes.subject FROM students RIGHT JOIN classes ON students.id =
classes.student_id;

Constraints

1. Add Primary Key: sql ALTER TABLE tablename ADD PRIMARY KEY
(columnname); Example: ALTER TABLE students ADD PRIMARY KEY (id);

2. Add Foreign Key: sql ALTER TABLE tablename ADD FOREIGN KEY
(columnname) REFERENCES othertable(columnname); Example: ALTER TABLE
classes ADD FOREIGN KEY (student_id) REFERENCES students(id);

3. Add Unique Constraint: sql ALTER TABLE tablename ADD UNIQUE

(columnname); Example: ALTER TABLE students ADD UNIQUE (name);
Indexing Commands

1. Create an Index: sql CREATE INDEX indexname ON tablename

(columnname); Example: CREATE INDEX idx_name ON students (name);

2. Drop an Index: sql DROP INDEX indexname ON tablename; Example: DROP

INDEX idx_name ON students;

Transaction Control Commands

1. Begin Transaction: sql BEGIN; Example: BEGIN;

2. Commit Transaction: sql COMMIT; Example: COMMIT;

3. Rollback Transaction: sql ROLLBACK; Example: ROLLBACK;

User Management Commands (MySQL)

1. Create User: sql CREATE USER 'username'@'localhost' IDENTIFIED BY

'password'; Example: CREATE USER 'newuser'@'localhost' IDENTIFIED BY
'password123';

2. Grant Privileges: sql GRANT ALL PRIVILEGES ON dbname.* TO

'username'@'localhost'; Example: GRANT ALL PRIVILEGES ON school.* TO
'newuser'@'localhost';

3. Revoke Privileges: sql REVOKE ALL PRIVILEGES ON dbname.* FROM

'username'@'localhost'; Example: REVOKE ALL PRIVILEGES ON school.*
FROM 'newuser'@'localhost';

4. Drop User: sql DROP USER 'username'@'localhost'; Example: DROP USER

'newuser'@'localhost';
Conditional, Filtering, Joins, Constraints,
Indexing, Transaction Control, and User
Management Commands

Author : AI PDF GPT Date : September 2024

Where Clause

The WHERE clause filters records that meet a specific condition.

Command: SELECT * FROM tablename WHERE condition;

Example: SELECT * FROM employees WHERE department = 'HR';

Explanation: Selects all records from the 'employees' table where the department is
'HR'.

Order By Clause

The ORDER BY clause sorts the result set in ascending or descending order.

Command: SELECT * FROM tablename ORDER BY columnname [ASC|DESC];

Example: SELECT * FROM employees ORDER BY salary DESC;

Explanation: Sorts records by salary in descending order.

Group By Clause

GROUP BY groups rows with the same values into summary rows.

Command: SELECT columnname, COUNT(*) FROM tablename GROUP BY columnname;

Example: SELECT department, COUNT(*) FROM employees GROUP BY department;

Explanation: Counts employees in each department.

Having Clause

The HAVING clause filters groups based on a condition.

Command: SELECT columnname, COUNT(*) FROM tablename GROUP BY columnname
HAVING condition;

Example: SELECT department, COUNT() FROM employees GROUP BY department

HAVING COUNT() > 5;

Explanation: Selects departments with more than 5 employees.

Inner Join

INNER JOIN selects records with matching values in both tables.

Command: SELECT columns FROM table1 INNER JOIN table2 ON table1.column =

table2.column;

Example: SELECT employees.name, departments.name FROM employees INNER JOIN

departments ON employees.department_id = departments.id;

Explanation: Retrieves employee names and their departments where IDs match.

Left Join

LEFT JOIN returns all records from the left table and matched records from the right.

Command: SELECT columns FROM table1 LEFT JOIN table2 ON table1.column =

table2.column;

Example: SELECT employees.name, departments.name FROM employees LEFT JOIN

departments ON employees.department_id = departments.id;

Explanation: Retrieves all employees and departments, even those without matches.

Right Join

RIGHT JOIN returns all records from the right table and matched records from the left.

Command: SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column =

table2.column;

Example: SELECT employees.name, departments.name FROM employees RIGHT JOIN

departments ON employees.department_id = departments.id;

Explanation: Retrieves all departments, including those with no employees.

Full Join

FULL JOIN returns all records when there is a match in either table.

Command: SELECT columns FROM table1 FULL JOIN table2 ON table1.column =

table2.column;

Example: SELECT employees.name, departments.name FROM employees FULL JOIN

departments ON employees.department_id = departments.id;

Explanation: Retrieves all employees and departments, even if there is no match.

Add Primary Key

Adds a PRIMARY KEY, which uniquely identifies each record in a table.

Command: ALTER TABLE tablename ADD PRIMARY KEY (columnname);

Example: ALTER TABLE employees ADD PRIMARY KEY (id);

Explanation: Adds a primary key to the 'id' column of the employees table.

Add Foreign Key

Adds a FOREIGN KEY to link two tables.

Command: ALTER TABLE tablename ADD FOREIGN KEY (columnname) REFERENCES

othertable(columnname);

Example: ALTER TABLE employees ADD FOREIGN KEY (department_id) REFERENCES

departments(id);

Explanation: Links 'department_id' to 'id' in the 'departments' table.

Add Unique Constraint

Ensures that all values in a column are unique.

Command: ALTER TABLE tablename ADD UNIQUE (columnname);

Example: ALTER TABLE employees ADD UNIQUE (email);

Explanation: Ensures no two employees have the same email address.

Add Check Constraint

Ensures that all values in a column meet a specific condition.

Command: ALTER TABLE tablename ADD CHECK (condition);

Example: ALTER TABLE employees ADD CHECK (salary > 0);

Explanation: Ensures all employees have a positive salary.

Create Index

Indexes are used to retrieve data faster.

Command: CREATE INDEX indexname ON tablename (columnname);

Example: CREATE INDEX idx_salary ON employees (salary);

Explanation: Creates an index on the salary column to speed up queries.

Drop Index

Deletes an index from a table.

Command: DROP INDEX indexname ON tablename;

Example: DROP INDEX idx_salary ON employees;

Explanation: Removes the 'idx_salary' index from the employees table.

Begin Transaction

Starts a new database transaction.

Command: BEGIN;

Example: BEGIN;

Explanation: Begins a new transaction for multiple operations.

Commit Transaction

Saves the changes made in the current transaction.

Command: COMMIT;

Example: COMMIT;

Explanation: Commits the changes made during the transaction.

Rollback Transaction

Undoes the changes made in the current transaction.

Command: ROLLBACK;

Example: ROLLBACK;

Explanation: Rolls back changes, restoring the database to its previous state.

Create User

Creates a new database user.

Command: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Example: CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Explanation: Creates a new user with a specified password.

Grant Privileges

Gives specific privileges to a user.

Command: GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';

Example: GRANT ALL PRIVILEGES ON dbname.* TO 'newuser'@'localhost';

Explanation: Grants all privileges on the 'dbname' database to the user.

Revoke Privileges

Removes privileges from a user.

Command: REVOKE ALL PRIVILEGES ON dbname.* FROM 'username'@'localhost';

Example: REVOKE ALL PRIVILEGES ON dbname.* FROM 'newuser'@'localhost';

Explanation: Revokes all privileges on the 'dbname' database from the user.
Show Users

Lists all users in the database (MySQL only).

Command: SELECT User FROM mysql.user;

Example: SELECT User FROM mysql.user;

Explanation: Retrieves a list of all database users in MySQL.

Drop User

Deletes a user from the database.

Command: DROP USER 'username'@'localhost';

Example: DROP USER 'newuser'@'localhost';

Explanation: Removes the user from the database.