DB 면접 예상 질문) "다음 상황 별 SQL 쿼리를 작성해주세요."

1.  Given a database with table members(id, name). Find the name of all members whose name start with 'h' or 
'H'. The final answer must have only column (name).

SELECT FROM members WHERE name LIKE 'h%' OR LIKE 'H%';

 

2.  A company's database maintains table salesperson(id, name) and s_order(order_date, cust_id , sale_id) to keep track of marketing. Find the names of salesperson that have more than 1 order assuming each salesperson has unique id. Your final table should contain the number of orders and name of salesperson as columns.

SELECT
    salesperson.name AS 'name of salesperson',
    COUNT(*) AS 'number of orders'
FROM
	s_order
JOIN
	salesperson ON s_order.sale_id = salesperson.id
GROUP BY
	salesperson.name, salesperson.id
HAVING
	COUNT(*) > 1;

 

3. A college database maintains a table students(name, dept, score). Find the names of all the students who have scores greater than those of at least one student in ECE department

SELECT name FROM students s1 WHERE EXISTS (SELECT 1 FROM students s2 WHERE s2.dept = 'ECE' AND s2.score < s1.score);

 

4. A college database maintains a table students( name, dept, score). Find the department with highest average score. Display it's average score as well

SELECT AVG(score) AS avg FROM students GROUP BY dept ORDER BY avg DESC LIMIT 1;

 

5. The owner of a music store maintains a database of albums with table album (album, band, cost). Seeing the appreciation for pink floyd and led zeppelin from his customers, he decided to increase the album cost by 50 percent for all the albums of both the bands. Write a query to update the costs.

UPDATE album
SET cost = cost * 1.5
WHERE band IN ('Pink Floyd', 'Led Zeppelin');

 

6. An online gaming website required registration of participants. A database was designed to maintain records in table participant(id , name, age). Initially id was of integer type. Later it was decided to make id of type text for easy usability. Modify the table schema to do the needful.

ALTER TABLE participant
MODIFY COLUMN id text

 

7. A government agency maintains a database that stores the addresses of all the people living in the country. The data is stored in the table – tbl_address_data(sNo, personId, Address). The address contains a zip code – a six-digit numeric code unique to every location in the country. In the address, the zip code can be obtained by searching for the keyword – ‘zip’ (case insensitive) followed by space(s), dash (-) or both and then the numeric code. Write a query to parse the given addresses and extract the list of address does not have any zip code.

SELECT Address FROM tbl_address WHERE Address NOT REGEXP 'zip[\s-]*[0-9]{6}'

 

From: https://service.shl.com/docs/Automata%20SQL%20Sample%20Report.pdf

 

그 외:

- SQL 처리 순서:

1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. ORDER BY
8. LIMIT/OFFSET

- SQL subquery:

• FROM (subquery) AS alias
  • subquery = SELECT abc FROM def
• SELECT a, b, (subquery) AS Avg
  • subquery = SELECT AVG(cost) FROM items
• WHERE Salary = (subquery);
  • subquery = SELECT MAX(Salary)
• WHERE DepartmentID IN (subquery);
  • subquery = SELECT DepartmentID FROM