Sql: An Introduction to the sql
Sql
What query deletes all patients that are younger than or equal to 60 years old from the table 'patients'?
DELETE FROM patients WHERE age ≤ 60What query updates the age of every patient that isn't yet 60 years old to 60 in the table 'patients'?
A: UPDATE patients(age) VALUES (60) WHERE age ≤ 60
B: UPDATE patients SET age = 60 WHERE age < 60
C: UPDATE patients(age) VALUES (60) WHERE age < 60
D: UPDATE patients SET age = 60 WHERE age ≤ 60Which command imports a MYSQL backup for the schema 'players' contained in 'backup.sql' using the 'root' user account through the command line?
A: mysql -u root -p players > backup.sql
B: mysqlimportdb -u root -p players < backup.sql
C: mysql -u root -p players < backup.sql
D: mysql -user root -p players < backup.sqlHow can you ensure that your table 'players' with the column 'age' contains no values under 18 in PostgreSQL?
A: Introduce a REQUIRE constraint in the table schema
B: It isn't possible in PostgreSQL
C: Make the age column a PRIMARY KEY
D: Introduce a CHECK constraint in the table schemaWhat is the correct way to add foreign keys for the table 'patients' so that field 'id' references 'patient_id' of the table 'records'
A: ALTER TABLE patients ADD CONSTRAINT patient_record_id FOREIGN KEY (id) REFERENCES records(patient_id)
B: ALTER TABLE patients ADD FOREIGN KEY patient_record_id(id) REFERENCES records(patient_id)
C: ADD FOREIGN KEY TO patients ON patient_record_id(id) = records(id)
D: ADD FOREIGN KEY TO patients ON patient_record_id(id) REFERENCES records(id)See the attached SQL query. What does it do?
A: It throws an error: There are two sums nested (one sum call in another sum call)
B: It calculates the overall cumulative visit time for a day in seconds
C: It calculates the cumulative seconds a website is visited per a day in seconds
D: It calculates the cumulative seconds a website is visited per visitor on average in secondsAnalyze the attached query. What does it do?
A: It finds the times a habit was executed the farthest away from the first habit tracked
B: It counts how many distinct habits have been tracked and at what time they were tracked
C: It lists the ranges and range lengths over distinct habits with no gaps
D: It counts how many total habits happened on the same day, and lists which the first and last events of that day wereScenario
現在
select now()
列出訂單項目（依據 數量*單價 排列）
select *, quantity * price as subtotal from order_items order by subtotal desc limit 3
依據上述篩選並查看年
select id, DATE_FORMAT(created_at, '%Y') as created_year, quantity * price as subtotal from order_items order by subtotal desc limit 3
列出特定日期的資料 ➡️ 連結
select * from orders where cast(created_at as date) = '2021-02-27'
select * from orders where cast(created_at as date) >= '2021-02-27' and < '2021-02-28'
列出任一日 ➡️連結
select DATE(DATE_SUB(NOW(), INTERVAL ROUND(RAND(1)*10) DAY)) as date
列出2020年資料
select * from orders where DATE_FORMAT(created_at, '%Y') = '2020'
列出2020年各月份交易次數前三高的ATM代碼，並顯示哪個月份、交易總金額
- 使用 db view & partition
- 使用 union
select 1 as month, customer_id, count(*) as count from orders
where year(created_at) = '2021' and month(created_at) = '1' group by customer_id order by count desc limit 3
union all
select 2 as month, customer_id, count(*) as count from orders
where year(created_at) = '2021' and month(created_at) = '2' group by customer_id order by count desc limit 3
union all
-- ...
union all
select 12 as month, customer_id, count(*) as count from orders
where year(created_at) = '2021' and month(created_at) = '12' group by customer_id order by count desc limit 3
