commandsyntax

COMMANDS : 
#create a database  
create database database_name; 
--------------------------------------------------------------------------------------------------------------------------------------
#delete a database                                                                                           {DROP = DDL}
drop database database_name; 
--------------------------------------------------------------------------------------------------------------------------------------
#create a table                                                                                             {CREATE = DDL}
create table table_name( 
column1 datatype_column1 table_constraint1,    #table_constraint1 : NOT NULL /AUTO INCREMENT
column2 datatype_column2 table_constarint1,
column2 datatype_column2 table_constarint1,
column2 datatype_column2 table_constarint1,
table_constraints2 ( ) );                       #table_constraint2 : PRIMARY KEY / FOREIGN KEY / UNIQUE 
--------------------------------------------------------------------------------------------------------------------------------------
#delete a table                                                                                             
drop table table_name; 
--------------------------------------------------------------------------------------------------------------------------------------
# SELECT query                                                                                               {SELECT = DQL} 
CASE 1:  select column1, column2, ... from table_name;  #selects columns specified from the table 
CASE 2:  select * from tebla_name   # selects all columns from the table 
-------------------------------------------------------------------------------------------------------------------------------------
# WHERE clause  --- used to filter records (extrct only those resords that fulfill the specified condition)  {WHERE = DQL}
SELECT column_name  
FROM table_name                                                                                               {FROM = DQL}
WHERE condition = True; 

Eg : SELECT first_name 
     FROM Student 
     WHERE city = 'Pune'; 
-------------------------------------------------------------------------------------------------------------------------------------
#AND, OR, NOT Statements    ---- if we need to add two or more conditions in the where clause then we can use these operators 

#AND 
SELECT column_name 
FROM table_name 
WHERE condition1 = TRUE AND condition2 = TRUE ;

#OR 
SELECT column_name 
FROM table_name 
WHERE condition1 = True or condition2 = False; /( WHERE condition1 = False or condition2 = True;)/ (WHERE condition1 = True or condition2 = True;) 

#NOT 
SELECT column_name 
FROM table_name 
WHERE NOT condition = True;    #gives the output where condition is not true 
------------------------------------------------------------------------------------------------------------------------------------
#AGGREGATE FUNCTIONS --- COUNT, SUM, AVERAGE, MINIMUM, MAXIMUM                                           {typically associated with DQL} 

COUNT - returns the number of rows that specify the criteria.  
SELECT COUNT(column_name) FROM table_name WHERE condition = TRUE; / SELECT COUNT(column_name) FROM table_name; 

AVG - returns the average value of numeric column 
eg:  SELECT AVG(price) FROM pizzas; 

SUM - returns the total sum of that particular column  #if applied on a non numeric col returns 0 

MIN - returns the minimum value from the selected column 

MAX - returns the largest value from the selected column 
------------------------------------------------------------------------------------------------------------------------------------
# GROUP BY --- used to arrange a similar type of functionality of data into a group                                {GROUP BY = DQL} 
#you need a aggregate function to be inserted for the group by to work 
syntax:  
select column_name(s) 
from table_name 
where condition 
group by column name;

#HAVING -- used to place condition where we need to decide which group will be the part of final result-set        {HAVING = DQL}
~ where clause cound not be used with aggregate function so having is used. 
syntax: 
select column_name(s) 
from table_name 
where condition 
group by column name(s)
having condition;

eg: select  pizza_type_id , price from pizzas 
having price >20; 

#ORDER BY -- used to sort the results in ascending or descending order                                             {ORDER BY = DQL} 
# DESC --> DESCENDING , asc --> ASCENDING 
eg: select pizza_id , sum(price) 
from pizzas 
order by sum(price) desc; 
# you dont need the aggregate function for order by you can use it on a non numeric column as well 
----------------------------------------------------------------------------------------------------------------------------------
#NULL VALUES(field with no value) - null does not mean the value is 0 
IS NULL - operator is used to test the empty values 
syntax : 
select col_name 
from table_name 
where col_name is null; 

IS NOT NULL - returns item that have not null values 
----------------------------------------------------------------------------------------------------------------------------------
#INSERT INTO                                                                                                     {INSERT INTO = DML}
INSERT INTO table_name ( column1, column2 ...) 
VALUES ( int , 'varchar(20)' , 'varchar(50)', 'text'), 
( int , 'varchar(20)' , 'varchar(50)', 'text'),
( int , 'varchar(20)' , 'varchar(50)', 'text'); 
---------------------------------------------------------------------------------------------------------------------------------
# UPDATE -- used to modify rows in a table                                                                           {UPDATE = DML}
syntax: 
update table_name 
set column1 = value1 , 
column2 = value 2.... 
where condition ; 
--------------------------------------------------------------------------------------------------------------------------------------
#DELETE -- used to delete rows that are no longer required from the table                                          {DELETE = DML}
syntax: 
DELETE FROM table_name 
WHERE condition; 
-------------------------------------------------------------------------------------------------------------------------------------
#used for filtering data based on specified conditions in the WHERE clause 

#IN operator -- used to specify multiple values inside the where clause 
syntax : 
select col_name 
from table_name 
where col_name is in (value1, value2, ...); 

#BETWEEN -- will select a particular value within the specified range, 
eg; select pizza_type_id , price 
from pizzas
where price between 15 and 20; 
----------------------------------------------------------------------------------------------------------------------------------
#ALLIASES IN SQL - giving a table or a column a temporary name do that it helps when the query is complex 
syntax : 
select col_name AS alias_name              #column aliasing 
from table_name 
where col_name is null

syntax : 
select col_name 
from table_name AS alias_name                  #table aliasing 
where col_name is null
-----------------------------------------------------------------------------------------------------------------------------------
Operators

1. In (used with where, for multiple values)   
eg: 
select first_name, dep from employee
where job_id in ('j1','j2')
output: first name | dep 
        Smith      | 10 


2. Comparison operators >,<,=,>=,<=
eg: 
select first_name,dept,salary from employee where salary > 1000; 
output: 
first_name | dept | salary 
Smith      | 10   | 2000 
Arun       | 30   | 5000 
Saket      | 50   | £9999999999999999999999999999999999999999999 PS 

3. LIKE 
-- search condition can contain either character or numbers 
-- % denotes zero or more characters 
-- _ denotes one character 
SQL > select dept_name,loc from dept where loc like 'c%' ;   #(starts wit c%  for ends with c it will be %c) 
SQL > select dept_name, loc from dept where loc like 'chen_ _ _ ' ; 


4. CASE expression (if then else) 
SELECT CASE (column_name) 
WHEN condition1 then result1 
WHEN cndition2 then result2 
ELSE result 
END 
FROM table_name 

# '=' operator does not work with NULL , If you want to check for null use IS NULL or IS NOT NULL