SQL - Most used commands.

sql-most-used.sql

-- Selecting Rows Where the Case of a Text Value Is Not Important
SELECT *
FROM MEMBER M
WHERE UPPER(M.MEMBERTYPE) = 'SENIOR'

-- Finding the Members with No Value for MemberType
SELECT * 
FROM MEMBER M
WHERE M.MEMBERTYPE IS NULL

-- Finding the Members with Value for MemberType
SELECT * 
FROM MEMBER M
WHERE M.MEMBERTYPE IS NOT NULL

-- Retrieving a List of Unique Membership Types
SELECT DISTINCT M.MEMBERTYPE

-- Retrieving the Members in Order of LastName
SELECT *
FROM MEMBER M
ORDER BY M.LASTNAME

-- Retrieving the Number of Senior Members
SELECT COUNT(*) 
FROM MEMBER M
WHERE M.MEMBERTYPE = 'Senior'

-- Retrieving the Senior Members Ordered by LastName and Then FirstName
SELECT *
FROM MEMBER M
ORDER BY M.LASTNAME, M.FIRSTNAME
FROM MEMBER M

-- Retrieving the Number of Records with a Non-Null Value for Handicap
SELECT COUNT(HANDICAP) FROM MEMBER

-- Retrieving the Number of Different Values for Handicap
SELECT COUNT(DISTINCT HANDICAP) FROM MEMBER

-- Cartesian product
SELECT *
FROM MEMBER M, TYPE T

-- Cartesian with where
SELECT *
FROM MEMBER M, TYPE T
WHERE M.MEMBERTYPE = T.TYPE

-- Simple INNER JOIN
SELECT *
FROM MEMBER M INNER JOIN TYPE T ON M.MEMBERTYPE = T.TYPE

-- Select using cartesian
SELECT *
FROM MEMBER M, ENTRY E, TOURNAMENT T
WHERE M.MEMBERID = E.MEMBERID AND E.TOURID = T.TOURID

-- Select using inner join
SELECT M.MEMBERID, M.LASTNAME, M.FIRSTNAME, E.TOURID, E.YEAR, T.TOURNAME, T.TOURTYPE
FROM ( MEMBER M INNER JOIN ENTRY E ON M.MEMBERID = E.MEMBERID )
INNER JOIN TOURNAMENT T ON E.TOURID = T.TOURID

-- Using IN
SELECT * 
FROM MEMBER 
WHERE MEMBERID IN (118, 138, 153)

-- Using NOT IN
SELECT * 
FROM MEMBER 
WHERE MEMBERID NOT IN (118, 138, 153)

-- Nested Query
SELECT E.MEMBERID
FROM ENTRY E
WHERE E.TOURID IN (
	SELECT T.TOURID
	FROM TOURNAMENT T
	WHERE T.TOURTYPE = 'Open'
)

-- SQL Statement to Retrieve Members Who Have Entered a Tournament
SELECT *
FROM MEMBER M
WHERE EXISTS (SELECT * FROM ENTRY E WHERE E.MEMBERID = M.MEMBERID)

-- SQL Statement to Retrieve Members with Handicaps Less Than Barbara Olson’s
SELECT *
FROM MEMBER M
WHERE HANDICAP < (
	SELECT HANDICAP
	FROM MEMBER 
	WHERE LASTNAME = 'Olson' AND FIRSTNAME = 'Barbara'
)

-- Constant query
SELECT MemberID, 25, 2007
FROM Member
WHERE MemberType = 'Junior'

--  Inserting Entries for Juniors into Tournament 25 for 2007
INSERT INTO ENTRY (MEMBERID, TOURID, YEAR)
SELECT MEMBERID, 25, 2007
FROM MEMBER
WHERE MEMBERTYPE = 'Junior'

--  Deleting Entries from Tournament 25, 2007, for Low Handicap Members
DELETE FROM ENTRY
WHERE TOURID = 25 AND YEAR = 2007 AND
MEMBERID IN (SELECT MEMBERID FROM MEMBER WHERE HANDICAP < 20)

-- Joining an Additional Copy of the Member Table to See Names of Managers
SELECT M.MEMBERID, M.FIRSTNAME, M.LASTNAME, T.TEAMNAME, T.MANAGER, M2.FIRSTNAME, M2.LASTNAME
FROM MEMBER M
INNER JOIN TEAM T ON M.TEAM = T.TEAMNAME
INNER JOIN MEMBER M2 ON M2.MEMBERID = T.MANAGER

-- Join Entry and Member Tables First to Show the Names of Members
SELECT M.MEMBERID, M.LASTNAME, M.FIRSTNAME, COUNT(*) AS NUMENTRIES
FROM ENTRY E INNER JOIN MEMBER M ON M.MEMBERID = E.MEMBERID
GROUP BY M.MEMBERID, M.LASTNAME, M.FIRSTNAME

-- Find Tournaments with Three or More Entries
SELECT TOURID, YEAR, COUNT(*) AS NUMENTRIES
FROM ENTRY
GROUP BY TOURID, YEAR
HAVING COUNT(*) >= 3

-- Find Members Who Have Entered More Than Four Open Tournaments
SELECT MEMBERID, COUNT(*) AS NUMENTRIES
FROM ENTRY E INNER JOIN TOURNAMENT T ON E.TOURID = T.TOURID
WHERE T.TOURTYPE = 'OPEN'
GROUP BY MEMBERID
HAVING COUNT(*) > 4

--Calling a proc
DECLARE 

PCODRET NUMBER(10);    
PDESRET VARCHAR2(32000); 
PCODERESULT VARCHAR2(32000); 

BEGIN 
USERNAME.PROC_ABC('A','B','C', 'D', PCODRET, PDESRET, PCODERESULT);
DBMS_OUTPUT.PUT_LINE('TEST');
END;
/