Question

​​​​​​Please give examples for my study guide, Thank You

• Be able to use the join syntax for queries, including outer joins.
• Know how to rename tables and columns.
• Know order by, asc, and desc, between, and not between.
• Know how to use the SQL set operations of union, intersect, and except. Know how to use all with these.
• Know how to do tuple comparison in queries.
• Know how to use operators with nulls, and know the is null and is not null operators.
• Be able to use the aggregate functions avg, count, min, max, and sum and know how to use them with group by and having.
• Know how aggregation works with null values.
• Know how to nest subqueries: in, not in, some, all, and exists. Know how to nest queries in the from clause. Be able to use the with clause. Be able to use scalar subqueries.
• Know how to use correlation variables in subqueries.
• Know how to use case in queries.
• Know how to insert, delete, and update using subqueries.

Answer 1

1. Know how to rename tables and columns

SYNTAX:

• FOR TABLE

ALTER TABLE table_name
RENAME TO new_table_name;

• FOR COLUMN

IN ORACLE:-

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

IN MySQL,MariaDB:-

ALTER TABLE table_name
CHANGE COLUMN old_name TO new_name;

Sample Table:

IPL

POSITION	PLAYER	POINTS
1	DHONI	23
2	KOHLI	21
3	KL RAHUL	20
4	ROHIT SHARMA	19

QUERY:

ALTER TABLE IPL RENAME COLUMN PLAYER TO CAPTAIN;

OUTPUT:

POSITION	CAPTAIN	AGE
1	MS DHONI	23
2	VIRAT KOHLI	21
3	KL RAHUL	20
4	ROHIT SHARMA	19

FOR TABLENAME:

QUERY:

ALTER TABLE IPL RENAME TO CAPTAIN_POINTS;

OUTPUT:

CAPTAIN_POINTS

ROLL_NO	FIRST_NAME	AGE
1	Ram	20
2	Abhi	21
3	Rahul	22
4	Tanu	19

2. Know order by, asc, and desc, between, and not between.

SYNTAX:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC | DESC;

EXAMPLE:

ID	YOUTUBERS			City	PostalCode	Country
1	COVID			WUHAN	12209	CHINA
2	ELVISH YADAV			GURUGRAM	122051	INDIA
3	TECHNICAL GURUJI			ABU DHABI	05023	DUBAI
4	PEWDIEPIE			NEW ORLEANS	12995	USA

QUERY:

SELECT * FROM Customers
ORDER BY Country;

OUTPUT:

ID	YOUTUBERS			City	PostalCode	Country
1	COVID			WUHAN	12209	CHINA
2	TECHNICAL GURUJI			ABU DHABI	05023	DUBAI
3	ELVISH YADAV			GURUGRAM	122051	INDIA
4	PEWDIEPIE			NEW ORLEANS	12995	USA

QUERY:

SELECT * FROM Customers
ORDER BY Country DESC;

OUTPUT:

ID	YOUTUBERS			City	PostalCode	Country
1	PEWDIEPIE			NEW ORLEANS	12209	USA
2	ELVISH YADAV			GURUGRAM	122051	INDIA
3	TECHNICAL GURUJI			ABU DHABI	05023	DUBAI
4	COVID			WUHAN	12209	CHINA

• 
  BETWEEN SYNTAX

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

• NOT BETWEEN SYNTAX

SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

EXAMPLE:

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18
2	Chang	1	1	24 - 12 oz bottles	19
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10
4	Chef Anton's Cajun Seasoning	1	2	48 - 6 oz jars	22
5	Chef Anton's Gumbo Mix	1	2	36 boxes	21.35

QUERY-

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

OUTPUT:-

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18
2	Chang	1	1	24 - 12 oz bottles	19
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10
15	Genen Shouyu	6	2	24 - 250 ml bottles	15.5

NOT BETWEEN:

QUERY:

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

OUTPUT:-

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
4	Chef Anton's Cajun Seasoning	2	2	48 - 6 oz jars	22
5	Chef Anton's Gumbo Mix	2	2	36 boxes	21.35
6	Grandma's Boysenberry Spread	3	2	12 - 8 oz jars	25
7	Uncle Bob's Organic Dried Pears	3	7	12 - 1 lb pkgs.	30

• Know how to use the SQL set operations of union, intersect, and except.

FOR UNION:

SYNTAX-

SELECT * FROM TABLE1 
UNION
SELECT * FROM TABLE2;

EXAMPLE:

The TOPPLAYER table,

ID	NAME
1	DHONI
2	KOHLI

The FLOPPLAYER table,

ID	NAME
2	KOHLI
3	KL RAHUL

QUERY:

SELECT * FROM TOPPLAYER
UNION
SELECT * FROM FLOPPLAYER;

OUTPUT:

ID	NAME
1	DHONI
2	KOHLI
3	KL RAHUL

FOR INTERSECT:

SYNTAX:

SELECT * FROM TABLE1
UNION
SELECT * FROM TABLE2;

EXAMPLE:

QUERY:

SELECT * FROM TOPPLAYER
INTERSECT
SELECT * FROM FLOPPLAYER;

OUTPUT:

ID	NAME
2	KOHLI

FOR EXCEPT OR MINUS:

SYNTAX:

SELECT * FROM TABLE1
EXCEPT
SELECT * FROM TABLE2;

EXAMPLE:

QUERY:

SELECT * FROM TOPPLAYER 
MINUS
SELECT * FROM FLOPPLAYER;

OUTPUT:

ID	NAME
1	DHONI
3	KL RAHUL

• Know how to use all with these.

SYNTAX:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);

EXAMPLE:

PLAYERPOINTS

PLAYERID	PLAYERNAME			TEAM	POINTS
1	DHONI			CSK	18
2	KOHLI			RCB	19
3	R SHARMA			MI	10
4	KL RAHUL			KXIP	22

IPLPOINTS

TEAMID	TEAMNAME	PLAYERID	POINTS
1	CSK	11	12
2	RCB	42	10
3	MI	72	5
4	KXIP	14	9

QUERY:

SELECT PLAYERNAME
FROM PLAYERPOINTS
WHERE TEAMID = ALL (SELECT TEAMID FROM IPLPOINTS WHERE POINTS= 10);

OUTPUT:

Number of Records: 0

PLAYERNAME

* WE ARE ONLY ALLOWED TO ANSWER FIRST FOUR PARTS*

SO. KINDLY SORRY,FOR OTHER REMAINING PARTS.