SQL Basics and Database Concepts

What is SQL?

SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. SQL enables you to create, retrieve, update, and delete data from databases.

Key Concepts in SQL

Database

A database is a collection of organized data, typically stored in tables, that can be easily accessed, managed, and updated.

Table

A table is a collection of related data stored in rows and columns. Each table is identified by a name, and each row in the table is a record containing data.

Record

A record is a row in a table, containing specific data for the columns (fields) in that table. For example, in the Customers table, each row represents a customer, with columns for CustomerID, CustomerName, ContactName, Address, etc.

Column

A column is a field in a table that holds a specific type of data (e.g., name, address, age).

Some of The Most Important SQL Commands

• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

-- Step 1: Select the database
USE niteshsynergy;

-- Step 2: Create the Customers table
CREATE TABLE IF NOT EXISTS Customers (
   CustomerID INT PRIMARY KEY,
   CustomerName VARCHAR(100),
   ContactName VARCHAR(100),
   Address VARCHAR(150),
   City VARCHAR(50),
   PostalCode VARCHAR(20),
   Country VARCHAR(50)
);

-- Step 3: Insert the data
INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
   (1, 'Alfreds Futterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),
   (2, 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Avda. de la Constitución 2222', 'México D.F.', '05021', 'Mexico'),
   (3, 'Antonio Moreno Taquería', 'Antonio Moreno', 'Mataderos 2312', 'México D.F.', '05023', 'Mexico'),
   (4, 'Around the Horn', 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),
   (5, 'Berglunds snabbköp', 'Christina Berglund', 'Berguvsvägen 8', 'Luleå', 'S-958 22', 'Sweden');
 
to get the distinct count of countries from the Customers table is:
 select count(distinct country) from customers;

Select all customers with a CustomerID greater than 80:

SELECT * FROM Customers    
WHERE CustomerID > 80 ;    

Here’s a detailed explanation of each operator that can be used in the WHERE clause with examples:

1. = (Equal)

The = operator is used to filter records that exactly match a specified value.

• Example:

sql

SELECT * FROM Customers WHERE Country = 'Germany';

This will return all customers whose Country is exactly 'Germany'.

2. > (Greater than)

The > operator is used to find records where the value in a column is greater than the specified value.

• Example:

sql

SELECT * FROM Customers WHERE CustomerID > 3;

This will return all customers with CustomerID greater than 3.

3. < (Less than)

The < operator is used to find records where the value in a column is less than the specified value.

• Example:

sql

SELECT * FROM Customers WHERE CustomerID < 3;

This will return all customers with CustomerID less than 3.

4. >= (Greater than or equal)

The >= operator is used to find records where the value in a column is greater than or equal to a specified value.

• Example:

sql

SELECT * FROM Customers WHERE CustomerID >= 3;

This will return all customers with CustomerID greater than or equal to 3.

5. <= (Less than or equal)

The <= operator is used to find records where the value in a column is less than or equal to a specified value.

• Example:

sql

SELECT * FROM Customers WHERE PostalCode <= '05021';

This will return all customers with PostalCode less than or equal to '05021'.

6. <> or != (Not equal)

The <> (or != in some databases) operator is used to find records where the value in a column is not equal to the specified value.

• Example:

sql

SELECT * FROM Customers WHERE Country <> 'Germany';

This will return all customers whose Country is not 'Germany'.

7. BETWEEN (Between a certain range)

The BETWEEN operator is used to find records where a column’s value falls within a specified range (inclusive).

• Example:

sql

SELECT * FROM Customers WHERE CustomerID BETWEEN 3 AND 5;

This will return all customers with CustomerID values between 3 and 5 (inclusive).

8. LIKE (Search for a pattern)

The LIKE operator is used to search for a pattern in a column. It often works with wildcard characters:

• % represents zero or more characters.
• _ represents exactly one character.
• Example:
• Example with _:

sql

SELECT * FROM Customers WHERE CustomerName LIKE 'A%';

This will return all customers whose CustomerName starts with 'A'.

sql

SELECT * FROM Customers WHERE CustomerName LIKE '_r%';

This will return all customers whose CustomerName has 'r' as the second letter.

9. IN (To specify multiple possible values for a column)

The IN operator is used to specify a list of possible values for a column.

• Example:

sql

SELECT * FROM Customers WHERE Country IN ('Germany', 'Mexico', 'Sweden');

This will return all customers whose Country is either 'Germany', 'Mexico', or 'Sweden'.

Summary

• =: Exact match of a value.
• >: Greater than.
• <: Less than.
• >=: Greater than or equal to.
• <=: Less than or equal to.
• <> or !=: Not equal.
• BETWEEN: A range of values.
• LIKE: Pattern matching with wildcards.
• IN: Multiple possible values.

The SQL ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order.
SELECT * FROM Products    
ORDER BY Price;    

SELECT * FROM Products    
ORDER BY Price DESC ;    

select * from products order by price asc, ProductName desc;    

select * from customers where country='Mexico' and customerName like 'a%';    


1. Fetching the nth record from the top:

For nth record from the top (generalized):

For MySQL and PostgreSQL (which supports LIMIT and OFFSET): sql Copy code