SQL Query and Sub Query
Duration : 00:02:00
SQL Queries in SQL Server
SQL Server allows you to perform powerful database operations using SQL queries. You can retrieve, modify, and manipulate data in a structured way to meet various business needs.Example
-- Select all employees from the 'Employees' table
SELECT * FROM Employees;
SQL Queries and the SELECT Sentence
The SELECT statement in SQL Server is fundamental for querying data. It retrieves specific columns or expressions from one or more tables and can be customized with various clauses for filtering and sorting.Example
-- Retrieve names and salaries of employees earning over $50,000
SELECT Name, Salary FROM Employees WHERE Salary > 50000;
SQL Queries to filter data using the WHERE command
The WHERE clause in SQL Server allows you to filter rows from a table based on specified conditions, making it easy to extract specific data subsets.Example
-- Retrieve orders placed by 'CustomerID' 123
SELECT * FROM Orders WHERE CustomerID = 123;
SQL Queries with aggregate functions and the use of the GROUP BY statement
SQL Server supports aggregate functions like SUM, AVG, COUNT, etc., to perform calculations on grouped data. The GROUP BY statement is used to group rows based on specified columns.Example
-- Calculate the total sales amount for each product category
SELECT Category, SUM(Price) AS TotalSales FROM Products GROUP BY Category;
SQL Queries to get data from multiple tables
SQL Server can join multiple tables to retrieve related data. Common join types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.Example
-- Retrieve customer names and their corresponding orders
SELECT Customers.Name, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
SQL Subqueries with SELECT Statement
Subqueries in SQL Server are nested queries that can be used within another query to retrieve data based on the results of the inner query.Example
-- Find employees with the highest salary
SELECT Name FROM Employees WHERE Salary = (SELECT MAX(Salary) FROM Employees);
Subqueries with the INSERT Statement
Subqueries can also be used with the INSERT statement in SQL Server to insert data into a table based on the result of a subquery.Example
-- Insert orders for products with low stock
INSERT INTO OrderQueue (ProductID, Quantity)
SELECT ProductID, 10 FROM Products WHERE Stock < 20;
Subqueries with the UPDATE Statement
Subqueries can be used with the UPDATE statement to modify data in one table based on information from another table or a subquery.Example
-- Update employee salaries by 10% for those in the 'Sales' department
UPDATE Employees
SET Salary = Salary * 1.1
WHERE Department = 'Sales';
Subqueries with the DELETE Statement
Subqueries can be used with the DELETE statement to remove rows from a table that meet certain conditions specified in the subquery.Example
-- Delete orders placed by customers who have not made a purchase in the last year
DELETE FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE LastPurchaseDate < DATEADD(year, -1, GETDATE()));
Still have some questions? Let's discuss.
CONTACT US