Hi All,
Some basic SQL queries.
1.Create a Table
Syntax:CREATE TABLE table_name
Eg-:CREATE TABLE Employee(id integer,name varchar(10),age integer,address varchar(10),salary integer,date Date);
2.
Insert values to Table
Syntax:INSERT INTO table_name(column name1,....column name) VALUES(value1,.....value
Eg-:INSERT INTO Employee(id, name, age, address, salary,date) VALUES( 1,'Riyas',18,'address1',1000,'1-june-1992'), ( 2,'Muhammed',19,'address2',2000,'1-june-1993')
3.
Update Table
Syntax: UPDATE table_name Set column_name=value where column_name=some_value
Eg-:UPDATE Employee SET name='Siddharth,address='address3' WHERE name='Riyas';
4.Selecting from Table
Syntax:Select * from table_name
Eg-:Select * from Employee
It will give the complete Table with data.
Eg:-SELECT name,age FROM Employee WHERE address = 'address3';
Eg:-SELECT name,age FROM Employee WHERE address <> 'address3';
Eg:-SELECT name,age FROM Employee WHERE date >= '1-july-1992';
Eg:-SELECT name,age FROM Employee WHERE (date >= '1-june-1992') AND (date <= '15-december-1993');
Eg:-SELECT name,age FROM Employee where date BETWEEN '1-june-1992' AND '15-december-1993';
Eg:-SELECT name,age FROM Employee WHERE date Not BETWEEN '1-june-1992' AND '15-december-1993';
Eg:-SELECT name,age FROM Employee WHERE address = 'abc' OR address = 'address1';
Eg:-SELECT name,age FROM Employee WHERE address IN ('abc', 'address1', 'Redmond');
Eg:-SELECT name,age FROM Employee WHERE address NOT IN ('abc', 'address1', 'Redmond');
Eg:-SELECT name,age FROM Employee WHERE address LIKE '_bc';(finds all three-letter address that end with 'bc')
Eg:-SELECT name,age FROM Employee WHERE name LIKE '%as';(finds all names whose last name ends with 'as')
Eg:-SELECT name,age FROM Employee WHERE LastName LIKE '%as%'; (finds all names whose last name includes 'as')
Eg:-SELECT name,age FROM Employee WHERE (name NOT LIKE 'a%') AND (name NOT LIKE 'b%');
Eg:-SELECT name,age FROM Employee ORDER BY address;
Eg:-SELECT name,age FROM Employee ORDER BY address DESC
Eg:-SELECT name,age FROM Employee ORDER BY address ASC, age DESC
Eg:-SELECT name,age FROM Employee Group BY address
5.ALTER TABLE
Syntax:-ALTER TABLE table_name ADD column_name date_type
Eg:-ALTER TABLE Employee ADD COLUMN DateOfBirth date;
Eg:-ALTER TABLE Employee ADD COLUMN address varchar(30);
Eg:-ALTER TABLE Employee DROP COLUMN DateOfBirth;
Eg:-ALTER TABLE Employee MODIFY COLUMN age varchar(30);
6.Finding and Deleting Duplicate Records
Eg:-SELECT id, name from Employee where name IN (SELECT name from Employee Group by name having (count(name) >1))
Eg:-DELETE FROM Employee WHERE name NOT IN (SELECT MIN(name) FROM Mytable GROUP BY name)
Eg:-delete FROM Employee WHERE id NOT IN (SELECT * FROM (SELECT MIN(n.id) FROM Employee n GROUP BY n.name))
7.Modifying Datetime
Eg:-select create_date + interval '4 hour' from Employee(adding 4hr with a date time object)
Eg:-select create_date + interval '8 second' from Employee(adding 8 seconds with a date time object)
Eg:-select create_date + interval '8 minute' from Employee(adding 8 minute with a date time object)
Eg:-select create_date + interval '4 day' from Employee(adding 4 days with a date time object)
8.Casting
Eg:-select CAST(create_date + interval '4 hour' AS date) as od_date from Employee(giving date as result)
Eg:-select CAST(age AS float) from Employee;
9.Extracting
Eg:-select EXTRACT(month FROM date_order) as VARCHAR(50) from Employee;
Eg:-select EXTRACT(year FROM date_order) as VARCHAR(50) from Employee;
10.Case Statement
Eg:-CASE WHEN address='abc' THEN 'Correct' ELSE 'wrong' END AS type from Employee;
Eg:-CASE WHEN (code = 'BASIC') THEN amount ELSE 0 END AS BASIC;
11.Sum function
Eg:-sum(age) AS total Age AS from Employee
Eg:-CASE WHEN sum(age)<>0 THEN ((sum(age)+sum(age))/sum(age))*100*-1 ELSE 0 END AS markup
Eg:-select CASE WHEN hr_payslip.xo_total_no_of_days > 0 THEN (hr_contract.xo_total_wage/hr_payslip.xo_total_no_of_days)
ELSE(hr_contract.xo_total_wage/1) END AS daily_salary from Employee;
12.Concatenation
Eg:-select CONCAT(CAST(EXTRACT(month FROM date_order) as VARCHAR(50)),'/',CAST(EXTRACT(year FROM date_order)
as VARCHAR(50))) from Employee;
13.Max function
Eg:-select max(date) from employee;
Eg:-select max(age) from employee;
14.to_char
Eg:-to_char(date_from,'yyyy/mm') as period from Employee,
15.AVG
Eg:-SELECT AVG(VacationHours)AS 'Average vacation hours',SUM(SickLeaveHours) AS 'Total sick leave hours' FROM Employee
WHERE JobTitle LIKE 'Vice President%';
16.CHECKSUM_AGG
Eg:-SELECT CHECKSUM_AGG(CAST(Quantity AS int)) FROM Employee;
17.Count
Eg:-SELECT COUNT(name) FROM Employee;
18.Grouping
Eg:-SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping' FROM Employee GROUP BY SalesQuota WITH ROLLUP;
19.MIN
Eg:-SELECT MIN(TaxRate) FROM employee;
20.Delete Command
Syntax:-Delete from table_name;
Eg:-DELETE from Employee;
21.Drop Command
Syntax:-DROP TABLE table_name;
Eg:-DROP TABLE Employee;
22.For avoiding division by zero
Eg:-(sum((l.qty * l.price_unit)) / NULLIF(sum((l.qty * u.factor)),0))
23.For null value replace in to zero
Eg:-COALESCE(l.cost_price, (0)::numeric)
Joining Tables
a)Table:CUSTOMERS
b)Table:ORDERS
Now, let us join these two tables in our SELECT statement as follows:
SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
This would produce the following result:
1.Left Joining
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table.
This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.
Syntax:
The basic syntax of LEFT JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;
Now, let us join these two tables using LEFT JOIN as follows:
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
2.Inner Joining
The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table 1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.
Syntax:
The basic syntax of INNER JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
Example:
Now, let us join these two tables using INNER JOIN as follows:
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
3.Right Joining
The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result, but with NULL in each column from left table.
This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.
Syntax:
The basic syntax of RIGHT JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;
Now, let us join these two tables using RIGHT JOIN as follows:
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
This would produce the following result:
4.Full Joining
The SQL FULL JOIN combines the results of both left and right outer joins.
The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
Syntax:
The basic syntax of FULL JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;
Now, let us join these two tables using FULL JOIN as follows:
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
This would produce the following result:
5.Self Joining
The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
Syntax:
The basic syntax of SELF JOIN is as follows:
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;
Now, let us join this table using SELF JOIN as follows:
SELECT a.ID, b.NAME, a.SALARY FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;
This would produce the following result:
6.Cartesian Product
The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.
Syntax:
The basic syntax of CARTESIAN JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM table1, table2 [, table3 ]
Now, let us join these two tables using CARTESIAN JOIN as follows:
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS, ORDERS;
This would produce the following result:
