🔍[SQL] SQL Basic Syntax

Understanding SQL Basics

🚀SQL (Structured Query Language) is a powerful tool for managing and manipulating databases. This guide will introduce you to the fundamental SQL commands and how to use them effectively.

Key commands

1 SELECT

• The SELECT statement is used select data from a database
• Syntax : SELECT column1, column2 FROM table_name;
• Example : SELECT name, age FROM users;

2 WHERE

• The WHERE clause is used to filter records
• Syntax : SELECT column1, column2 FROM table_name WHERE condition;
• Operators:
  • = : Equals
  • > : Greater than
  • < : Less than
  • >=: Greater than or equal to
  • <=: Less than or equal to
  • <> or != : Not equal
• Example : SELECT * FROM users WHERE age > 30;

AND, OR NOT

• These operators are used to filter records based on multiple conditions
• Syntax:
  • AND : SELECT * FROM users WHERE age > 30 AND city = 'Seoul';
  • OR : SELECT *FROM users WHERE age > 30 OR city = 'Seoul';
  • NOT : SELECT * FROM users WHERE NOT city = 'Seoul';

ORDER BY

• The ORDER BY clause is used to sort the result set in ascending or descending order.
• Syntax : SELECT column1, column2 FROM table_name ORDER BY column1 ASC|DESC;
• Example : SELECT * FROM users ORDER BY name ASC;

JOIN

• JOIN clause is used to combine rows from two or more tables, based on a related column.
• Types of Joins:
  • INNER JOIN : Returns records that have matching values in both tables.
  • LEFT JOIN or LEFT OUTER JOIN : Returns all records from the left table, and the matched records from the right table.
  • RIGHT JOIN or RIGHT OUTER JOIN : Returns all records from the right table, and the matched records from the left table.
  • FULL JOIN or FULL OUTER JOIN : Returns all records when there is a match in either left or right table
• Syntax : SELECT columns FROM table1 INNER JOIN table2 ON table.column = table2.column;
• Example : SELECT users.name, orders,amount FROM users INNER JOIN orders ON users.user_id = orders.user_id;

GROUP BY

• The GROUP BY statement groups rows that have the same values into summary rows.
• Syntax : SELECT column1, COUNT(column2) FROM table_name GROUP BY column;
• Example : `SELECT city, COUNT(*) FROM users GROUP BY city;

HAVING

• The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
• Syntax: SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 HAVING COUNT(column2) > 5;
• Example : SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 5;

UNION

• The UNION operator is used to combine the result-set of two or more SELECT statements
• Syntax: SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2;
• Example : SELECT name FROM customers UNION SELECT name FROM suppliers;

INSERT INTO

• The INSERT INTO statement is used to insert new records in a table
• Syntax: INSERT INTO table_name (column1, column2) VALUES (value1, value2);
• Example : INSERT INTO users (name, age) VALUES ('John', 28);

UPDATE

• The UPDATAE statement is used to modify the existing records in a table
• Syntax:UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
• Example :UPDATE users SET age = 29, WHERE name = 'John';

DELETE

• The DELETE statement is used to delete existing records in table.
• Syntax : DELETE FROM table_name WHERE condition;
• Example : DELETE FROM users WHERE name = 'John';