Data Science Explorer

The SELECT INTO Statement copies data from one table into a new table. Syntax SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition; Example The following SQL statement copies only the German customers into a new table: SELECT * INTO CustomersGermany FROM Customers WHERE Country = 'Germany'; Exercises You have a table called Employees with columns EmployeeID, FirstName, LastName, ..

SQL ANY and ALL Operators The ANY and ALL operators allow you to compare between a single column value and a range of other values. SQL ANY Operator The ANY operator gives you a boolen value as a result. It means that the condition will be true if the operation is true for any of the values in the range. It returns TRUE if any of the subquery is true. ANY Syntax SELECT column_name(s) FROM table_..

The SQL EXISTS Operator It is used to test for the existence of any record in a subquery and gives you TRUE if there is the record that you are looking for. EXISTS Syntax SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition); Exercises 1. Consider a SQL query that returns TRUE and lists the employees with a salary less than 50000. Write the SQL q..

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions. HAVING Syntax SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s); Example The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers: SELECT COUNT(CustomerID), Co..

The GROUP BY statement groups rows that have the same values and it is usually used in aggregate functions ( COUNT (), MAX(), MIN(), SUM(), AVG()). GROUP BY Syntax SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s); Example Write an SQL query to find the total number of orders placed by each customer in the "Orders" table. Group the results by c..

The UNION operator is ues to combine the result-set of two or more SELECT statements. In other words, it is basically combining two or more tables. There are three rules for UNION operator: Every SELECT statement within UNION must have the same number of columns They must have have similar data types The columns in every SELECT statement must also be in the same order UNION Syntax SELECT column_..

The FULL OUTER results in all records when there is a match in left or right table records. FULL OUTER JOIN Syntax SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition; Example The following SQL statement selects all customers, and all orders: SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Cust..

The RIGHT JOIN keyword give you all records from the right table (table2). The result is 0 records from the left side, if there is no match. Syntax SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; Exercises Write an SQL query to retrieve a list of all product categories and the products within those categories. Use a RIGHT JOIN to ensure that all ca..

The LEFT JOIN returns all records from the left table (table 1). In other words, if there is no match with table 2, the result is 0 records from the right side. Syntax SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; Example SELECT Brands.BrandName, Orders.OrderID FROM Brands LEFT JOIN Orders ON Brands.BrandsID = Orders.OrderID ORDER BY Brands.BrandN..

The INNER JOIN gives you values that are only rows with a match in both tables. When you join two tables, there should be a table that is a standard (main table). Please refer to the following diagram: Syntax SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; Naming the columns Specify the column names: SELECT Customers.CustomerID, Customers.Age, Cust..