Description:
This KB Article covers the Basic Knowledge about the SQL Queries. How to execute the simple queries in SQL Server Database.
QUERY 1: HOW TO CREATE NEW DATABASE?
CREATE DATABASE RetailDatabase
QUERY 2: HOW TO CONNECT TO ABOVE DATABASE?
USE RetailDatabase
QUERY 3: HOW TO CREATE TABLE FOR DATA STORAGE?
CREATE TABLE tblProducts
(
ProductID INTEGER,
ProductName CHARACTER(40),
Price INT,
Size Char(10)
)
QUERY 4: HOW TO STORE DATA INTO ABOVE TABLE?
INSERT INTO tblProducts VALUES (1001, ‘CAPS’, 10, ‘S’)
QUERY 5: HOW TO STORE DATA INTO ABOVE TABLE?
INSERT tblProducts VALUES (‘1002’, ‘CAPS’, ’12’, ‘M’);
QUERY 6: HOW TO INSERT MULTIPLE ROWS IN ABOVE TABLE?
INSERT tblProducts VALUES
(1003, ‘BALLS’, 11, ‘S’), (1004, ‘BALLS’, 12, ‘M’), (1005, ‘GLOVES’, 5, ‘X’)
QUERY 7: HOW TO VERIFY ABOVE INSERTED TABLE DATA?
SELECT * FROM tblProducts
QUERY 8 : HOW TO REPORT ALL PRODUCTS WITH SIZE = S
SELECT * FROM tblProducts WHERE SIZE = ‘S’
QUERY 9 : HOW TO REPORT ALL PRODUCTS EXCEPT SMALL (S) SIZE?
SELECT * FROM tblProducts WHERE SIZE != ‘S’
QUERY 10: HOW TO REPORT ALL PRODUCTS WITH IDs 1002 OR 1004?
SELECT * FROM tblProducts WHERE ProductID = 1002 OR ProductID = 1004
QUERY 11: HOW TO REPORT ALL PRODUCTS WITH IDs 1002 OR 1004?
SELECT * FROM tblProducts WHERE ProductID IN (1002, 1004)
QUERY 12: HOW TO REPORT ALL PRODUCTS WITH IDs EXCEPT 1002 AND 1004?
SELECT * FROM tblProducts WHERE ProductID NOT IN (1002,1004)
QUERY 13: HOW TO REPORT ALL PRODUCTS WITH IDs EQUAL TO OR ABOVE 1002?
SELECT * FROM tblProducts WHERE ProductID >= 1002
QUERY 14: HOW TO REPORT ALL PRODUCTS WITH IDs EQUAL TO OR BELOW 1005?
SELECT * FROM tblProducts WHERE ProductID <= 1005
QUERY 15: HOW TO REPORT ALL PRODUCTS WITH BETWEEN 1002 AND 1005?
SELECT * FROM tblProducts WHERE ProductID >= 1002 AND ProductID <= 1005
QUERY 16: HOW TO REPORT ALL PRODUCTS WITH BETWEEN 1002 AND 1005?
SELECT * FROM tblProducts WHERE ProductID BETWEEN 1002 AND 1005
QUERY 17: HOW TO REPORT ALL PRODUCTS NOT BETWEEN 1002 AND 1005?
SELECT * FROM tblProducts WHERE ProductID NOT BETWEEN 1002 AND 1004
QUERY 18 : HOW TO REPORT PRICES FOR ALL PRODUCTS?
SELECT PRICE FROM tblProducts
QUERY 19 : HOW TO REPORT UNIQUE PRICES FOR ALL PRODUCTS?
SELECT DISTINCT PRICE FROM tblProducts
QUERY 20 : HOW TO REPORT MAXIMUM VALUE OF ALL PRICES FOR ALL PRODUCTS?
SELECT MAX(PRICE) FROM tblProducts
QUERY 21 : HOW TO REPORT ALL PRODUCT DETAILS ABOVE MAXIMUM PRICE VALUE?
SELECT * FROM tblProducts WHERE PRICE = 12
QUERY 22 : HOW TO REPORT ALL PRODUCT DETAILS WITH MAXIMUM PRICE VALUE?
SELECT * FROM tblProducts WHERE PRICE = ( SELECT MAX(PRICE) FROM tblProducts )
QUERY 23 : HOW TO REPORT ALL PRODUCT DETAILS WITH MINIMUM PRICE VALUE?
SELECT * FROM tblProducts WHERE PRICE = ( SELECT MIN(PRICE) FROM tblProducts )
QUERY 24: HOW TO COMBINE THE OUTPUT OF ABOVE TWO QUERIES?
SELECT * FROM tblProducts WHERE PRICE = ( SELECT MAX(PRICE) FROM tblProducts )
UNION
SELECT * FROM tblProducts WHERE PRICE = ( SELECT MIN(PRICE) FROM tblProducts )
QUERY 25: HOW TO COMBINE THE OUTPUT OF ABOVE TWO QUERIES?
SELECT * FROM tblProducts WHERE PRICE = ( SELECT MAX(PRICE) FROM tblProducts )
UNION ALL
SELECT * FROM tblProducts WHERE PRICE = ( SELECT MIN(PRICE) FROM tblProducts )