Structured Query Language (SQL)

  • Intro

    Every single employee--no matter what field you are in--should know how to pull subsets of data from a database. If you can't do this, you will be at a disadvantage because you will always have to wait for someone from IT to do it for you which adds greater cost to the company.

    Most databases you will have access to are based on a language called Structured Query Language (SQL) . SQL is a language for creating, modifying, selecting, and deleting the tables, rows, fields, and data stored within a DBMS. Unless you are the database administrator, the primary use you will have for SQL is in "selecting" (i.e. querying) the data. Fortunately, SQL is fairly consistent across all DBMSs. These videos cover how to perform various skills in SQL.

    To follow along with the videos below, begin by downloading the data found here: Gold Star HT database

  • SELECT and FROM

    Write a SQL statement that will return all employee names (first, last) only.

    SELECT firstname, lastname
    FROM employee;
                                                
  • WHERE

    Write a SQL statement that will find all employees who have an “Average” Performance Rating.

    SELECT firstname, lastname, performance
    FROM employee
    WHERE performance = "Average";
                                                
  • Numbers vs Text, >=, ORDER BY

    Write a SQL statement that will show all the employees that make $75,000 or more. Display Last Name, First Name, Hire Date, Salary. Order by Salary in descending order.

    SELECT  lastname, firstname, hiredate, Salary
    FROM employee
    WHERE salary >= 75000
    ORDER BY salary DESC;
                                                
  • Dates

    Write a SQL statement that will show all employees hired after 12/31/1995. Order by hire date in ascending order. Display Last Name and Hire Date.

    SELECT  lastname, hiredate
    FROM employee
    WHERE HireDate > #12/31/1995#
    ORDER BY hiredate ASC;
                                                
  • Joins

    Write a SQL statement that will show all employees who work in Georgia. Show Last Name, First Name, and Location City fields.

    inner join: table.primarykey = table.foreignkey

    SELECT lastname, firstname, locationcity
    FROM employee, location
    WHERE location.locationID = employee.locationID
    AND state = "Georgia";
                                                

    Write a SQL statement that shows all the managers and the city where they are a manager. Display Location City, First Name, Last Name, Position Title. Order by Location City. This uses multiple joins

    SELECT locationcity, firstname, lastname, positiontitle
    FROM employee, location, position
    WHERE location.locationID = employee.locationID
    AND employee.positionID = position.positionID
    AND positiontitle LIKE "*manager"
    ORDER BY locationcity;
    
                                                
  • Functions, AVG(), and GROUP BY

    Write a SQL statement that shows the average salary of the employees in each location. Display LocationID, Location City, AverageSalary.

    SELECT location.locationID, locationcity, AVG(salary) AS AverageSalary
    FROM employee, location
    WHERE location.locationID = employee.locationID
    ORDER BY lastname
    GROUP BY location.locationID, locationcity;
                                                
  • COUNT()

    Write a SQL statement that shows how many employees work in the New York City and Denver locations. Display Location City and Total Employees.

    SELECT locationcity, COUNT(ssn) as totalemployees
    FROM employee, location
    WHERE location.locationID = employee.locationID
        AND (locationcity = "New York City"
        OR locationcity = "Denver")
    GROUP BY locationcity;
                                                
  • SUM()

    Write a SQL statement that shows the total salary of the employees for each location. Display Location City and Total.

    SELECT locationcity, SUM(salary) as Total
    FROM employee, location
    WHERE location.locationID = employee.locationID
    GROUP BY locationcity;
                                                
  • BETWEEN

    Write a SQL statement that shows each of the employees hired between 1/1/1997 and 12/31/2002 (inclusive).

    SELECT *
    FROM employee
    WHERE hiredate BETWEEN #1/1/1997# and #12/31/2002#;
                                                
  • Tricky Parentheses

    Write a SQL statement that will find employees who make more than $20,000 but no more than $80,000 who have a "Good" performance rating, as well as anyone who works in the Miami office. Display FirstName, LastName, Salary, Performance, and LocationCity. Order by Salary.

    SELECT firstname, lastname, salary, performance, locationcity
    FROM employee, location
    WHERE location.locationID = employee.locationID
        AND (salary BETWEEN 20000 AND 80000
        AND performance = "Good"
        OR locationcity = "Miami")
    ORDER BY salary;
                                                

    Write a SQL statement that will find all Managers who were hired after the year 2002, as well as anyone who works in the Chicago office. Display FirstName, LastName, HireDate, LocationCity, and Position Title. Order by HireDate in descending order.

    SELECT firstname, lastname, hiredate, locationcity, positiontitle
    FROM employee, location, position
    WHERE location.locationID = employee.locationID
        AND employee.positionID = position.positionID
        AND (positiontitle LIKE "*manager"
        AND hiredate >= #1/1/2003#
        OR locationcity = "Chicago")
    ORDER BY hiredate DESC;
                                                

    Select employees with the first name of Holly and received a “Good” performance rating, but in the same query select all employees who received an “Average” performance rating. Display SSN, first name, last name, and performance rating in the results.

    SELECT ssn, firstname, lastname, performance
    FROM employee
    WHERE (firstname = "Emily"
        AND performance = "Good")
        OR performance = "Average";
                                                
  • NULL

    IS NULL

    We need to clean our data and make sure every employee record is complete. Are there any employees who are missing data in the gender field? Write a SQL statement to display them.

    SELECT *
    FROM employee
    WHERE gender IS NULL;
                                                

    IS NOT NULL

    Show all of the employees who are managers and have received a performance rating (field is not empty). Show the last name, salary, performance rating, location, and position title.

    SELECT lastname, salary, performance, locationcity, positiontitle
    FROM employee, location, position
    WHERE location.locationID = employee.locationID
        AND employee.positionID = position.positionID
        AND positiontitle LIKE "*manager"
        AND performance IS NOT NULL;
                                                
  • Calculated Fields

    Calculate the difference between the current salary and the maximum salary of each Regional Manager. Name the calculated field “SalaryDifference.” Show last name, salary, maximum salary, and SalaryDifference.

    SELECT lastname, salary, maxsalary, (maxsalary - salary) as SalaryDifference
    FROM employee, position
    WHERE employee.positionID = position.positionID
        AND positiontitle = "Regional Manager";
                                                
  • MIN()

    Create a Summary Query to Display the average and minimum salary of all job positions. Display job position, average salary, and minimum salary.

    SELECT positiontitle, AVG(salary) as AverageSalary, MIN(salary) as MinimumSalary
    FROM employee, position
    WHERE employee.positionID = position.positionID
    GROUP BY positiontitle;
                                                
  • DISTINCT()

    What countries do the tweets originate from? Do not list duplicates and sort the countries alphabetically. HINT: use DISTINCT. Note, this uses a different database than the previous problems. We cannot give you the database for this example. However, try to apply what you're learning in this video to the GoldStar database that you already have.

    SELECT DISTINCT(Country)
    FROM location
    ORDER BY Country;
                                                
  • TOP

    Who has the top 10 Klout scores? Sort them by score descending. Return their names and scores. HINT: use TOP10. Note, this uses a different database than the previous problems.

    SELECT TOP 10 klout, fname, lname
    FROM Tweeter, Tweet
    WHERE Tweeter.TweeterID = Tweet.TweetID
    ORDER BY klout DESC;
                                                

    What were the 20 tweets with the most negative sentiment? List the sentiment, text, Klout score, and the number of retweets that each got

    SELECT TOP 20 Sentiment, text, klout, retweetcount
    FROM Tweet
    ORDER BY sentiment ASC;
                                                

    Now show the 20 tweets with the most positive sentiment? List the sentiment, tweet, Klout score, and the number of retweets that each got. By the way, what do you learn from this? What is the effect of the tone of your tweet on the number of retweets that you get?

    SELECT TOP 20 Sentiment, text, klout, retweetcount
    FROM Tweet
    ORDER BY sentiment DESC;
                                                
  • UNION

    What is the average retweet count for tweets that include a URL link versus those that don't?

    SELECT AVG(retweetcount) AS AverageWithURL
    FROM Tweet
    WHERE text LIKE "*http*"
    UNION
    SELECT AVG(retweetcount) AS AverageWOurl
    FROM Tweet
    WHERE text NOT LIKE "*http*";
                                                
  • Additional Employee Database Practice

    Show the first and last name only of employees who received a “Good” performance rating and earn more than $50,000.

    SELECT firstname, lastname
    FROM employee
    WHERE performance = "good"
        AND Salary > 50000;
                                                

    From the EMPLOYEE table, select the SSN, first and last names, and performance rating of all employees who work at location 1.

    SELECT ssn, firstname, lastname, performance
    FROM employee
    WHERE locationID = 1;
                                                

    Select records for employees who have either “Emily” or “Frank” as their first name. Show the first and last name of these employees as well as their salary.

    SELECT firstname, lastname, salary
    FROM employee
    WHERE firstname = "Emily"
        OR firstname = "Frank";
                                                

    Create a query that shows only the last and first name, location city, and position title of employees who earn less than $50,000.

    SELECT lastname, firstname, locationcity, positiontitle
    FROM employee, location, position
    WHERE location.locationID = employee.locationID
        AND employee.positionID = position.positionID
        AND salary < 50000;
    
                                                

    Select all of the employees who received either an “Average” or “Poor” performance rating and work in Chicago. Show first name, last name, and performance rating from the Employee table and location from the Location table.

    SELECT firstname, lastname, performance, locationcity
    FROM employee, location
    WHERE location.locationID = employee.locationID
        AND (performance = "Average"
        OR performance = "Poor""
        AND locationcity = "Chicago";
                                       

    Show the last name, salary, position title, and location city of all the Managers who earn more than $50,000 and work in Chicago or Miami.

    SELECT lastname, salary, positiontitle, locationcity
    FROM employee, location, position
    WHERE location.locationID = employee.locationID
        AND employee.positionID = position.positionID
        AND positiontitle LIKE "*manager"
        AND salary > 50000
        AND (locationcity = "Chicago"
        OR locationcity = "Miami");
                                       

    Create a query that selects Trainees who earn less than $19,000 or more than $22,000. Show first and last name, city location, salary, and position title.

    SELECT firstname, lastname, locationcity, salary, positiontitle
    FROM employee, location, position
    WHERE location.locationID = employee.locationID
        AND employee.positionID = position.positionID
        AND (salary < 19000 OR salary > 22000)
        AND positiontitle = "Trainee";
    
                                       
  • Additional Twitter Database Practice

    Create a list of all retweets. Include only the Tweet. Order it by Tweet alphabetically descending

    SELECT text
    FROM Tweet
    WHERE IsReshare = TRUE
    ORDER BY text DESC;
                                                

    Create a list of all Tweeters. Order by Lname ascending and then Fname descending

    SELECT Fname, Lname
    FROM Tweeter
    ORDER BY Lname ASC, Fname DESC;
                                                

    Which tweets were retweeted at least 50 times? Give me their name, location country, state, city, the tweet itself, and the actual number of retweets

    SELECT Fname, Lname, country, state, city, text, retweetcount
    FROM Location, Tweet, Tweeter
    WHERE Tweet.TweeterID = Tweeter.TweeterID
        AND Tweet.LocationID = Location.LocationID
        AND retweetcount >= 50;
                                                

    How many tweets came from each country?

    SELECT country, COUNT(Tweet.LocationID) As TweetCount
    FROM Location, Tweet
    WHERE Tweet.LocationID = Location.LocationID
    GROUP BY country;
                                                

    What is the average Klout score by country? Return the country and Klout score.

    SELECT country, AVG(klout) as AverageKlout
    FROM Location, Tweet
    WHERE Tweet.LocationID = Location.LocationID
    GROUP BY country;
                                                

    Show me a list of Tweeters from each country outside of the US with Klout scores between 60 and 80. Order the list by country.

    SELECT DISTINCT Fname, Lname, country
    FROM Location, Tweet, Tweeter
    WHERE Tweet.TweeterID = Tweeter.TweeterID
        AND Tweet.LocationID = Location.LocationID
        AND Tweet.Klout BETWEEN 60 and 80
        AND location.country <> "United States"
    ORDER BY location.country;
                                                

    Which state (in the US) is using twitter the most? Show me a list of the number of tweets sent from each state, starting with the most

    SELECT state, COUNT(ID) AS TweetCount
    FROM Location, Tweet
    WHERE Tweet.LocationID = Location.LocationID
        AND Country = "United States"
    GROUP By state
    ORDER BY TweetCount;