Basic of SQL

Basic of SQL

USE    Sql_store ;

SELECT *

FROM  customers

WHERE state = "CA"

ORDERED BY first_name

 LIMIT 3 ;

• SQL is not a case-sensitive language.
• In mySQL, every statement must be terminated with a semicolon.

Comments

We use comments to add notes to our code.

   ---This is a comment and it won't get executed

SELECT Clause

SELECT (points *10 to 20 ) As discount_factor

FROM customers

Ordered of operations :

• Parenthesis
• Multiplication / division
• Addition / Subtraction

----Removing duplicates

SELECT DISTINCT state

FROM Customers

WHERE Clause

We use WHERE Clause to filter data.   

Comparison operators :

• Greater than                                         →                  >
• Greater than or equal to                       →                  >=
• Less than                                              →                  <
• Less than or equal to                            →                  <=
• Equal                                                    →                   =
• Not equal                                              →                   < >
• Not equal                                              →                   !=

Logical Operators

--AND   (both Condition must be True)

SELECT *

FROM customers

WHERE birthdate >'1990-01-01' AND points >1000

--OR (at least one condition must be True )

SELECT *

FROM customers 

WHERE birthdate >'1990-01-01' OR points > 1000

--NOT (to negate a condition)

SELECT *

FROM  customers

WHERE NOT (birthdate > '1990-01-01')

IN Operators

--Return customers in any of these states

-- : VA , NY , CA

SELECT

FROM customers

WHERE  state IN ('VA', 'NY', 'CA')

BETWEEN Operator

SELECT *

FROM customers

WHERE points BETWEEN 100 AND 200

LIKE Operator

--Returns customers who first name starts with b

SELECT *

FROM customers

WHERE first_name like 'b%'

• % : any number of characters
• _  : exactly one character

REGEXP Operator

--Returns customers whose First name starts --  with a

SELECT *

FROM customers

WHERE first-name REGEXP '^a'

• ^      :    beginning of a string
• $      :    end of a string
• |       :    logical OR
• [abc] : match any single characters
• [a-d]   : any characters from a to d

More examples

--Returns customers whose first-name ends with --EY or ON

WHERE first-name REGEXP  'ey$|on$'

--Returns customers whose first name starts---with My

--or contains SE

WHERE first-name REGEXP 'b[ru]'

--Returns customers whose first-name contains

--A followed by 

--R or ||

WHERE first-name REGEXP  'b[ru]'

IN NULL Operators

--Returns customers who don't have a phone 

--number

SELECT *

FROM customers

WHERE phone IS NULL

ORDERED BY Clause

--Sort customers by state (in ascending order ),

--and then by their first name (in descending ordered)

--ordered ) 

SELECT *

FROM customers 

ORDERED BY state , first name, DESC

LIMIT Clause

--Returns only & 3 customers 

SELECT *

FROM customers 

LIMIT 3

--Skip 6 customers and return 3*

SELECT *

FROM customers

LIMIT 6,3

INNER Join

SELECT *

FROM customers c

JOIN orders 0

        ON c customer = id=0.customer_id

OUTER Joins

SELECT*

FROM customers c

LEFT JOIN orders 0

     ON c.customer_id = 0.customer_id

USING Clause

If column names are exactly same , you can simplify the join with the USING Clause.

SELECT *

FROM customers c

JOIN orders 0

         USING (customer_id)

Cross Joins

--combine every color with every size 

SELECT*

FROM colors

CROSS JOIN sizes

Unions

--combine records from multiple results sets 

SELECT name , address

FROM customers

UNION

SELECT name , address 

FROM clients

Inserting Data

--Insert a single record

INSERT INTO customers (first-name , phone , points)

VALUES ('codes , NULL , DEFAULT )

--Insert multiple single records

INSERT INTO customers (first name , phone , points)

VALUES 

           ('codes', NULL, DEFAULT),

          ('LERAN','345', 90)