Last active
July 5, 2016 12:50
-
-
Save danieltnaves/c04c83365cf6f27d3b5981f7ec18fee7 to your computer and use it in GitHub Desktop.
SQL - Most used commands.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; | |
/ | |
Author
danieltnaves
commented
Jun 30, 2016
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment