[SQL]: Some Examples

SQL

09/18/2019

SQL online editor

Brief SQL query examples

SQL

SELECT * FROM Customers

Displays all customers, ordered by CustomerID by default

SQL

SELECT * FROM products
ORDER BY price DESC;

Displays all products in decreasing order of price

SQL

SELECT
    customerName,
    COUNT(*) AS 'numOrders'
FROM customers
INNER JOIN orders
    ON orders.customerID = customers.customerID
GROUP BY customers.customerID;

Create customerName and numOrders columns from 2 different entities; customers and orders

SQL

SELECT firstName,
    COUNT(*) as numorders
FROM employees
INNER JOIN orders ON employees.employeeID = orders.employeeID
GROUP BY employees.employeeID;

Similar example to above

More complex examples

SQL

SELECT
    username,
    photos.id,
    photos.image_url,
    COUNT(*) AS total
FROM photos
INNER JOIN likes ON likes.photo_id = photos.id
INNER JOIN users ON photos.user_id = users.id
GROUP BY photos.id
ORDER BY total DESC
LIMIT 1;

SQL

SELECT first_name,
    last_name,
    Count(rating)                       AS COUNT,
    Ifnull(MIN(rating), 0)              AS MIN,
    Ifnull(MAX(rating), 0)              AS MAX,
    Round(Ifnull(Avg(rating), 0), 2)    AS AVG,
    CASE
        WHEN Count(rating) >= 10 THEN 'POWER USER'
        WHEN Count(rating) > 0 THEN 'ACTIVE'
        ELSE 'INACTIVE'
    end                                 AS STATUS
FROM reviewers
    LEFT JOIN reviews
        ON reviwers.id = reviews.reviewer_id
GROUP BY reviewers.id;

SQL

SELECT 1 + 1;

Examples of dynamic SQL

SQL

DECLARE @value varchar(10)
SET @value = 'intStep'
DECLARE @sqlText nvarchar(1000);

SET @sqlText = N'SELECT ' + @value + ' FROM dbo.tblBatchDetail'
Exec (@sqlText)

SQL

DECLARE @ColumnA nvarchar(max),
        @ColumnB nvarchar(max),
        @table nvarchar(max)
        @sql nvarchar(max)

SELECT  @ColumnA = N'UserID',
        @ColumnB = N'Salary',
        @table = N'Table'

SELECT @sql = N'SELECT ' +QUOTENAME(@ColumnA)+','+QUOTENAME(@ColumnB)+ ' FROM '+QUOTENAME(@table) +';'

EXEC sp_executesql @sql

WRITTEN BY

Ellis Min

Keeping a record

GitHub