SQL

SQL CLI commands

Here’s an extensive list of SQL commands and techniques, including commands for various SQL dialects and advanced operations.

1. SQL Basics

Create a Database:
```
CREATE DATABASE mydatabase;
```
Drop a Database:
```
DROP DATABASE mydatabase;
```

Create a Table:

CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

Drop a Table:
```
DROP TABLE mytable;
```

Insert Data:

INSERT INTO mytable (id, name, age) VALUES (1, 'John Doe', 30);

Select Data:
```
SELECT * FROM mytable;
```

Update Data:

UPDATE mytable SET age = 31 WHERE id = 1;

Delete Data:
```
DELETE FROM mytable WHERE id = 1;
```

2. Advanced SQL Techniques

Join Tables:

SELECT a.name, b.department
FROM employees a
JOIN departments b ON a.department_id = b.id;

Group By and Having:

SELECT department, COUNT(*) as count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Subqueries:

SELECT name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'HR');

Window Functions:

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

Common Table Expressions (CTEs):

WITH SalesCTE AS (
    SELECT product_id, SUM(amount) as total_sales
    FROM sales
    GROUP BY product_id
)
SELECT product_id, total_sales
FROM SalesCTE
WHERE total_sales > 1000;

3. MySQL Specific Commands

Show Databases:
```
SHOW DATABASES;
```
Show Tables:
```
SHOW TABLES;
```
Describe Table:
```
DESCRIBE mytable;
```
Show Create Table:
```
SHOW CREATE TABLE mytable;
```

Backup Database:

mysqldump -u username -p mydatabase > backup.sql

Restore Database:

mysql -u username -p mydatabase < backup.sql

4. PostgreSQL Specific Commands

Connect to Database:
```
psql -U username -d mydatabase
```
List Databases:
```
\l
```
List Tables:
```
\dt
```
Describe Table:
```
\d mytable
```

Backup Database:

pg_dump -U username mydatabase > backup.sql

Restore Database:

psql -U username mydatabase < backup.sql

5. SQLite Specific Commands

Open Database:
```
sqlite3 mydatabase.db
```
List Tables:
```
.tables
```
Describe Table:
```
PRAGMA table_info(mytable);
```

Backup Database:

sqlite3 mydatabase.db .dump > backup.sql

Restore Database:
```
sqlite3 mydatabase.db < backup.sql
```

6. Microsoft SQL Server Specific Commands

Connect to Database:

sqlcmd -S servername -U username -P password -d mydatabase

List Databases:
```
SELECT name FROM sys.databases;
```

List Tables:

SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE';

Describe Table:
```
sp_help mytable;
```

Backup Database:

BACKUP DATABASE mydatabase TO DISK = 'C:\backup\mydatabase.bak';

Restore Database:

RESTORE DATABASE mydatabase FROM DISK = 'C:\backup\mydatabase.bak';

7. Oracle SQL Specific Commands

Connect to Database:

sqlplus username/password@//hostname:port/service_name

List Databases:
```
SELECT name FROM v$database;
```
List Tables:
```
SELECT table_name FROM user_tables;
```
Describe Table:
```
DESCRIBE mytable;
```

Backup Database:

expdp username/password DIRECTORY=dump_dir DUMPFILE=mydatabase.dmp LOGFILE=mydatabase.log FULL=y

Restore Database:

impdp username/password DIRECTORY=dump_dir DUMPFILE=mydatabase.dmp LOGFILE=mydatabase.log FULL=y

8. NoSQL Databases

MongoDB

Connect to Database:
```
mongo
```
Show Databases:
```
show dbs;
```
Show Collections:
```
show collections;
```

Insert Document:

db.mycollection.insert({ name: "John Doe", age: 30 });

Find Document:

db.mycollection.find({ name: "John Doe" });

Update Document:

db.mycollection.update({ name: "John Doe" }, { $set: { age: 31 } });

Delete Document:

db.mycollection.remove({ name: "John Doe" });

Cassandra

Connect to Database:
```
cqlsh
```

Create Keyspace:

CREATE KEYSPACE mykeyspace WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 3};

Use Keyspace:
```
USE mykeyspace;
```

Create Table:

CREATE TABLE mytable (
    id UUID PRIMARY KEY,
    name TEXT,
    age INT
);

Insert Data:

INSERT INTO mytable (id, name, age) VALUES (uuid(), 'John Doe', 30);

Select Data:
```
SELECT * FROM mytable;
```

Update Data:

UPDATE mytable SET age = 31 WHERE id = some-uuid;

Delete Data:

DELETE FROM mytable WHERE id = some-uuid;

9. Advanced SQL Topics

Indexing

Create Index:

CREATE INDEX idx_name ON mytable (name);

Drop Index:
```
DROP INDEX idx_name;
```

Transactions

Begin Transaction:
```
BEGIN TRANSACTION;
```
Commit Transaction:
```
COMMIT;
```
Rollback Transaction:
```
ROLLBACK;
```

Stored Procedures and Functions

Create Stored Procedure:

CREATE PROCEDURE myprocedure (IN param1 INT, OUT param2 VARCHAR(100))
BEGIN
    SELECT name INTO param2 FROM mytable WHERE id = param1;
END;

Call Stored Procedure:
```
CALL myprocedure(1, @name);
```

Create Function:

CREATE FUNCTION myfunction (param1 INT) RETURNS VARCHAR(100)
BEGIN
    DECLARE result VARCHAR(100);
    SELECT name INTO result FROM mytable WHERE id = param1;
    RETURN result;
END;

Call Function:
```
SELECT myfunction(1);
```

Views

Create View:

CREATE VIEW myview AS
SELECT name, age
FROM mytable
WHERE age > 25;

Select from View:
```
SELECT * FROM myview;
```
Drop View:
```
DROP VIEW myview;
```

10. Security and User Management

Create User:

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

Grant Privileges:

GRANT SELECT, INSERT ON mydatabase.* TO 'username'@'hostname';

Revoke Privileges:

REVOKE INSERT ON mydatabase.* FROM 'username'@'hostname';

Show Grants:
```
SHOW GRANTS FOR 'username'@'hostname';
```
Drop User:
```
DROP USER 'username'@'hostname';
```

Set Password:

SET PASSWORD FOR 'username'@'hostname' = PASSWORD('newpassword');

11. SQL Optimization and Performance

Query Optimization

Analyze Query:

EXPLAIN SELECT * FROM mytable WHERE name = 'John Doe';

Use Indexes:

CREATE INDEX idx_name ON mytable (name);

Partition Tables:

CREATE TABLE mytable (
    id INT,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY (id, name)
)
PARTITION BY HASH(id) PARTITIONS 4;

Optimize Query:
```
OPTIMIZE TABLE mytable;
```

12. SQL Backup and Recovery

MySQL

Backup Database:

mysqldump -u username -p mydatabase > backup.sql

Restore Database:

mysql -u username -p mydatabase < backup.sql

PostgreSQL

Backup Database:

pg_dump -U username mydatabase > backup.sql

Restore Database:

psql -U username mydatabase < backup.sql

Oracle

Backup Database:

expdp username/password DIRECTORY=dump_dir DUMPFILE=mydatabase.dmp LOGFILE=mydatabase.log FULL=y

Restore Database:

impdp username/password DIRECTORY=dump_dir DUMPFILE=mydatabase.dmp LOGFILE=mydatabase.log FULL=y

SQL Server

Backup Database:

BACKUP DATABASE mydatabase TO DISK = 'C:\backup\mydatabase.bak';

Restore Database:

RESTORE DATABASE mydatabase FROM DISK = 'C:\backup\mydatabase.bak';

13. SQL Monitoring and Maintenance

MySQL

Check Table:
```
CHECK TABLE mytable;
```
Repair Table:
```
REPAIR TABLE mytable;
```
Analyze Table:
```
ANALYZE TABLE mytable;
```

PostgreSQL

Vacuum Table:
```
VACUUM mytable;
```
Analyze Table:
```
ANALYZE mytable;
```

14. SQL Data Import and Export

MySQL

Import Data:

LOAD DATA INFILE '/path/to/file.csv' INTO TABLE mytable
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Export Data:

SELECT * FROM mytable INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

PostgreSQL

Copy Data:

COPY mytable TO '/path/to/file.csv' DELIMITER ',' CSV HEADER;

Import Data:

COPY mytable FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;

15. SQL Reporting and Analytics

Basic Aggregations:

SELECT COUNT(*), AVG(age), MAX(age), MIN(age) FROM mytable;

Advanced Aggregations:

SELECT department, SUM(salary), AVG(salary) FROM employees
GROUP BY department
HAVING SUM(salary) > 50000;

Pivot Tables:

SELECT 
    department,
    SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) as Male,
    SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) as Female
FROM employees
GROUP BY department;

16. SQL Special Techniques and Functions

String Functions

Concatenate:

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

Substring:

SELECT SUBSTRING(name, 1, 5) FROM mytable;

Length:
```
SELECT LENGTH(name) FROM mytable;
```

Date Functions

Current Date:
```
SELECT CURRENT_DATE;
```

Date Difference:

SELECT DATEDIFF(CURRENT_DATE, hire_date) FROM employees;

Add Date:

SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) FROM employees;

17. SQL Programming Constructs

Conditional Logic

Case Statements:

SELECT name,
       CASE
           WHEN age < 18 THEN 'Minor'
           WHEN age BETWEEN 18 AND 65 THEN 'Adult'
           ELSE 'Senior'
       END as age_group
FROM mytable;

Loops and Iterations

While Loop:

DELIMITER //
CREATE PROCEDURE LoopExample()
BEGIN
    DECLARE x INT DEFAULT 0;
    WHILE x < 10 DO
        SET x = x + 1;
    END WHILE;
END//
DELIMITER ;

For Loop (in PL/pgSQL):

DO $$ 
DECLARE
    r record;
BEGIN
    FOR r IN SELECT * FROM mytable LOOP
        RAISE NOTICE '%', r.name;
    END LOOP;
END $$;

18. Database Design and Normalization

Normalization

First Normal Form (1NF):
- Eliminate duplicate columns from the same table.
- Create separate tables for each group of related data.
Second Normal Form (2NF):
- Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
Third Normal Form (3NF):
- Remove columns that are not dependent upon the primary key.

Relationships

One-to-Many Relationship:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Many-to-Many Relationship:

CREATE TABLE course_student (
    course_id INT,
    student_id INT,
    PRIMARY KEY (course_id, student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

This comprehensive guide covers a wide range of SQL commands and techniques, suitable for various SQL dialects and including advanced operations. It should provide a solid foundation for both basic and complex SQL tasks.

Name		Name	Last commit message	Last commit date
Latest commit History 3 Commits
README.md		README.md

DeadmanXXXII/SQL

Folders and files

Latest commit

History

Repository files navigation

SQL

1. SQL Basics

2. Advanced SQL Techniques

3. MySQL Specific Commands

4. PostgreSQL Specific Commands

5. SQLite Specific Commands

6. Microsoft SQL Server Specific Commands

7. Oracle SQL Specific Commands

8. NoSQL Databases

MongoDB

Cassandra

9. Advanced SQL Topics

Indexing

Transactions

Stored Procedures and Functions

Views

10. Security and User Management

11. SQL Optimization and Performance

Query Optimization

12. SQL Backup and Recovery

MySQL

PostgreSQL

Oracle

SQL Server

13. SQL Monitoring and Maintenance

MySQL

PostgreSQL

14. SQL Data Import and Export

MySQL

PostgreSQL

15. SQL Reporting and Analytics

16. SQL Special Techniques and Functions

String Functions

Date Functions

17. SQL Programming Constructs

Conditional Logic

Loops and Iterations

18. Database Design and Normalization

Normalization

Relationships

About

Resources

Stars

Watchers

Forks

Releases

Packages 0

Packages