[IS 201] SQL PROJECT

13.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 and makes more than, or equal to, $50,000. Display FirstName, LastName, HireDate, LocationCity, and Position Title. Order by HireDate in descending order.

1) SELECT firstname,lastname, hiredate, locationcity,positiontitle, salary 2)FROM employee 3)INNER JOIN position ON employee.positionid=position.positionid 4)INNER JOIN location ON employee.locationid=location.locationid 5)WHERE positiontitle LIKE "%Manager[*manager has to be singluar]" 6)AND hiredate>= '2003-01-01' 7) OR locationcity = 'Chicago' 8)AND salary >=50000[(*numbers don't need single quotes] 9)ORDERBY hiredate DESC

7.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.

1)SELECT Location City, firstname, lastname, position title 2)FROM employee 3)INNER JOIN location ON employee.location id=location.locationid 4)INNER JOIN position ON employee.positionid =position.positionid 5)WHERE positiontitle LIKE '%manager' 6)ORDER BY locationcity

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

1)SELECT employee.locationid(locationid가 dataset schema에 중복되니까), locationcity, AVG(salary) 2)FROM employee 3)INNER JOIN location ON employee.locationid=location.locationid 4)GROUP BY[*comes after our joins, after our WHERE clauses]employee.locationid,locationcity[anything that is selected, that doesn't have average or someother functions] 5)ORDER BY locationcity

[!]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.

1)SELECT firstname, lastname, salary, hiredate 2)FROM employee 3)WHERE salary >= 75000 4)ORDER BY salary DESC (ASC means ascending)

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

1)SELECT firstname,lastname,hiredate 2)FROM employee 3)WHERE hiredate BETWEEN '1997-01-01' AND '2002-12-31' 4)ORDERBY hiredate

Write a SQL statement that will find employees who make $20,000 or more 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.

1)SELECT firstname,lastname,salary, performance, and locationcity 2)FROM employee 3)INNER JOIN position ON employee.positionid=position.positionid 4)INNER JOIN location ON employee.locationid=location.locationid 4)WHERE salary BETWEEN 20000 AND 80000 5)AND performance = 'Good' 6)OR locationcity = 'Miami' 7)ORDER BY salary 8)보이는 query 중 하나가 문제에 맞는지 확인

[!]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.

1)SELECT last name, hiredate 2)FROM employee 3)WHERE hiredate > '1995-12-31' *single quote is common* 4)ORDER BY hiredate ASC

[!]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.

1)SELECT lastname, salary, performance, locationcity[!!], positiontitle 2)FROM employee 3)INNER JOIN location ON employee.locationid = location.locationid 4)INNER JOIN position ON employee.positionid = position.positionid 5)WHERE positiontitle LIKE '%Manager' AND performance IS NOT NULL[!!]

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

1)SELECT locationcity, COUNT[works on numbers and text based fields;whatever that is primary key](SSN) AS NumofEmployees 2)FROM employee 3)INNER JOIN location ON employee.locationid=location.locationid 4)WHERE locationcity ='New York City' 5) OR locationcity = 'Denver' 6)GROUP BY location city

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

1)SELECT locationcity, SUM(salary) AS Total 2)FROM employee 3)INNER JOIN location ON employee.locationid=location.locationid 4)GROUP BY locationcity

GROUP BY

AVG, COUNT, MIN, MAX, and SUM;We should note that the GROUP BY statement is only necessary for aggregate functions, such as AVG, COUNT, MIN, MAX, and SUM.

What are the top five revenue per capita(revenue/population) for any customer? in the following states: GA, WI, or AL? List cust_name, city_name,rounded revenue per capita(rpc) and customer state.

SELECT cust_name, city_name, ROUND(annual_revenue/population), customer.state FROM shipment JOIN customer ON shipment.cust_id=customer.cust_id JOIN city ON shipment.city_id=city.city_id WHERE customer.state [!!]IN ("GA","WI","AL") ORDER BY RRPC DESC[*because we're trying to get the top five*] LIMIT 5

How many customers have the word "Sales" in their name? How many of those are retailers? Display cust_name and cust_type

SELECT cust_name, cust_type FROM customer WHERE cust_name LIKE "%sales%" *when using wildcard, caps don't matter*

How many each type of customer are there? Display cust_type and total customers of that type.

SELECT cust_type, COUNT (cust_type)as total FROM customer GROUP BY cust_type

[!]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 50000

SELECT lastname, firstname, locationcity, positiontitle FROM employee INNER JOIN location ON employee.locationid = location.locationid INNER JOIN position ON employee.positionid = position.positionid WHERE salary < 50000

Write a SQL statement that will show all the employees that have a last name that starts with A or M. Display Last Name, First Name, and Performance. Order by Last Name.

SELECT lastname,firstname, performance 2)FROM employee 3)WHERE last name LIKE 'A%'(nothing or as many character as I want after the A) 4) OR last name LIKE 'M%' 5)ORDER BY lastname

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

나의 쿼리가 맞는지 체크하려면

run query 를 돌려본다. 나온 결과가 문제가 제시한 결과와 맞는지 체크한다.

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

"Write a"가 나오는게 화면 줄에 나와야하는 것. "Display"는 테이블 이름

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

*look at the data schema; performance is under employee* 1) SELECT firstname, lastname 2)FROM employee 3) WHERE performance ='Average' *check if Average is capitalized; WHERE is a criteria* 4) add 'performance' to "SELECT" *text based criteria is ' '*

[!!]Select all of the employees who received either an "Average" or "Poor" performance rating and work in Chicago. Show first name, last name, performance rating, and locationcity.(goldstar dataset, practice problem 2, #6)

1) SELECT firstname, lastname, performance, locationcity 2)FROM employee 3)[!]INNER JOIN location ON employee.locationid = location.locationid 4)[!왜 두번이나?]INNER JOIN position ON employee.positionid = position.positionid 5)WHERE [!](performance = 'Average' OR performance = 'Poor') [!]AND locationcity = 'Chicago' IMPORTANT: Notice that the parentheses are necessary to make this query work properly. Without them, you get results for employees who are 'Average' but don't work in 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.

1) SELECT lastname, salary, [!!]performance, positiontitle, locationcity 2)FROM employee 3)INNER JOIN location ON employee.locationid = location.locationid 4)INNER JOIN position ON employee.positionid = position.positionid WHERE positiontitle LIKE '%Manager' AND salary > 50000 AND (locationcity = 'Chicago' OR locationcity = 'Miami')

15.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.

1) SELECT lastname, salary, maxsalary, (maxsalary-salary)/maxsalary AS SalaryDifference 2)FROM employee 3)INNER JOIN position ON employee.positionid=position.positionid 4)WHERE positiontitle = 'Regional Manager'

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

1) firstname, --*check dataset schema*--last name 2)FROM employee

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

1.SELECT [attribute names] lastname, firstname, locationcity 2.FROM employee[table 1 name] 3. INNER JOIN location [table 2 name] ON employee[table1].location id[key] = location[table 2]. locationid[key] 4. WHERE state = 'GA'(*go to data sheet, check how location>state looks like*)

BETWEEN (INCLUSIVE)

BETWEEN AND

LEFT JOIN, RIGHT JOIN, INNER JOIN, FULL JOIN, ON

LEFTJOIN: returns all records from the left table, and the matched records from the right table RIGHTJOIN: returns all records from the right table, and the matched records from the left table (INNER)JOIN:Returns records that have matching values in both tables FULLJOIN: returns all records when there is a match in either left or right table OUTERJOIN:

14.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. (There are two methods)

METHOD A: 1) SELECT * 2) FROM employee 3) WHERE gender IS NULL METHOD B: 1) SELECT * 2) FROM employee 3) WHERE gender !=' ' METHOD C: 1) SELECT * 2) FROM employee 3) WHERE gender IS NOT NULL

What is the average annual revenue of customers in California, Utah, Washington, and New York? Hint: use ROUND() to remove decimals.

SELECT ROUND(AVG(annual_revenue)), customer.state FROM customer WHERE customer.state IN ("CA", "UT", "WA", "NY") GROUP BY customer.state

[!]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 and first and last names in the results.

SELECT SSN, firstname, lastname FROM employee WHERE firstname = 'Holly' AND performance = 'Good' OR performance[!*performance 항상 넣어야한다*] = 'Average'

How many shipments have been made to retailers in Utah?

SELECT cust_Type, customer.state, COUNT(ship_id)as totalShipped FROM shipment table JOIN customer ON shipment.cust_id=customer.cust_id WHERE customer.state ="UT" AND cust_type= "retailer" *look at the data summary; you can see the shipment table is the central table with all the foreign keys, so it should be the primary table. * GROUP BY[*everything that's up at SELECT row, but that is not in the aggregate function] cust_type, customer.state

Which company orders the most(has the most shipments)in California? List all customers in California in descending order by total shipments. (Hint: use COUNT, not SUM)

SELECT cust_name, COUNT(ship_id) FROM shipment JOIN customer ON shipment.cust_id=customer.cust_id WHERE customer.state="CA" GROUP BY cust_name [!]ORDER BY totalShipped DESC

AS

changes the name of the table (for example, "ssn" ->"NumofEmployees"ㄹㅗ 바뀜)

%A%

can have anything or nothing before the A and after the A, all it has to have is to have A somewhere

*conventional rules*

capitalize statements (such as SELECT, WHERE, and FROM) and functions (such as SUM, AVG, and MAX). *Write one criteria at a time.*

!=

is not

Mar%in

starts with Mar, ends with in

ON

tell how two tables are related to each other (primary key, foreign key relationship)