Data Science Explorer

Aliases are often used to give a table or a column in a table and they are created with AS keyword. Syntax When alias is used on column: SELECT column_name AS alias_name FROM table_name; When alias is used on table: SELECT column_name(s) FROM table_name AS alias_name; Using Aliases with a space character When you want spaces in aliases, you have to use [] or double quotes. SELECT Brandname AS [M..

The BETWEEN operator selects values within a given range and they can be numbers, text, or dates. This operator is inclusive: begin and end values are included. Syntax SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; Example Selects all products with a price between 100 and 150: SELECT * FROM Products WHERE Price BETWEEN 100 and 150; NOT BETWEEN When you want to..

The IN operator allows you to specify multiple values in a WHERE clause. It is a shortcut for multiple OR conditions. Syntax SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...); Example Return all the students from 'South Korea' and 'North Korea' SELECT * FROM Students WHERE Country IN ('South Korea', 'North Korea'); NOT IN When you use NOT IN operator, you return al..

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two different wildcards often used in conjunction with the LIKE operator: The percent sign % represents zero, one, or multiple characters The underscore sign _ represents one, single charater Syntax SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern; The_Wildcard The _ wildca..

The AVG() function returns the average value of a numeric column. Syntax SELECT AVG(column_name) FROM table_name WHERE condition; Example Find the average price of all cups. SELECT AVG(Price) FROM Cups; ** NULL is ignored** Add a WHERE Clause You can specify conditions by using WHERE clause. Example Return the average price of sweaters in category 90: SELECT AVG(Price) FROM Sweaters WHERE Custom..

The SUM () function gives you the totla sum of a numeric column. Syntax SELECT SUM(column_name) FROM table_name WHERE condition; Example Write an SQL query to return the sum of all the "TotalAmount" fields in the "Invoices" table. SELECT SUM(TotalAmount) AS "Total Sum" FROM Invoices; Add a WHERE Clause This is to specify conditions: Example Return the number of orders made for the product with C..

The COUNT () function gives the number of rows that matches a specified criterion. Example Find the total number of brands in the Brands table: SELECT COUNT(*) FROM Brands; Syntax SELECT COUNT(column_name) FROM table_name WHERE condition; Add a WHERE cluase Example Find the number of food where Price is higher than $11: SELECT COUNT (*) FROM FOODS WHERE Price > 11; Specify Column Instead of usin..

The MIN() function gives you the smallest value of the selected column and MAX() gives you the biggest value. Syntax MIN () SELECT MIN(column_name) FROM table_name WHERE condition; MAX() SELECT MAX(column_name) FROM table_name WHERE condition; MIN () Example Find the lowest grade: SELECT MIN(Grade) FROM Students; MAX() Example Find the highest grade: SELECT MAX(Grade) FROM Students; Set Colum Na..

The DELETE statement is used to remove existing records in a table. DELETE Syntax DELETE FROM table_name WHERE condition; ** WARNING: Be careful when you delete records in a table! If you omit the WHERE clause, all records in the table will be deleted. The Where clause in the DELETE statement. ** Example Delete the customer "Jack Lee" from the "Customers" table: DELETE FROM Customers WHERE Custo..

The UPDATE statement is used to modify the existing records in a table. UPDATE Syntax UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; ** Tip: Notice the WHERE clause in the UPDATE statement.** UPDATE Table Example Update the first customer (CustomerID = 60) with a new contact person and a new city. UPDATE Customers SET ContactName = 'Pearl', City = 'Seoul' WHERE Cu..