SQL code copied to buffer
Group tasks by:
Support the project.
- 1. Get the actors
- 2. Get the languages list
- 3. Retrieve Actor Names
- 4. Retrieve All Departments
- 5. Staff names
- 6. Product categories
- 7. Ordered Languages List
- 8. Top 5 Longest Films
- 9. Find stuff members by condition
- 10. Get the sorted list of films with condition
- 11. Find film names by description
- 12. Retrieve Actors by Name
- 13. Find the average length of a movie
- 14. Find foreign employees
- 15. Ordered Movie Titles
- 16. Find clients starting with the letter "A"
- 17. Find clients starting with the letter "A" (2)
- 18. Find the minimal and maximal film rental cost
- 19. Top 10 Movies by Title
- 20. Find long movies
- 21. Calculate the area of a circle
- 22. Calculate circle perimeter
- 23. Find active customers
- 24. Movies with above-average rental rates
- 25. Movies with the highest replacement cost
- 26. Get list of clients
- 27. Unique Movie Ratings
- 28. Get the restricted films list
- 29. Get list of restricted films
- 30. Create new address record
- 31. Update the postal code
- 32. Remove customer records
- 33. Addresses Lacking Postal Codes
- 34. Addresses with Even Postal Codes
- 35. Build shared surnames list
- 36. Get airports data
- 37. Find long-range aircraft
- 38. Identify Palindrome Names
- 39. What is SQL?
- 40. What is DBMS?
- 41. What is RDBMS?
- 42. What is a Database?
- 43. What is ACID?
- 44. What are DQL commands?
- 45. What is index in SQL?
- 46. SQL Tables joins types
- 47. Choose join type
- 48. Perform price update
- 49. Update replacement cost
- 50. Order of execution of logical operators
- 51. Difference between UNION and UNION ALL
- 52. Display a list of departments
- 53. Find Employees by Department
- 54. Find employee salary
- 55. Employees with high salaries
- 56. Employees with Above-Average Salaries
- 57. Find customers with even numbers
- 58. Find customers by phone prefix
- 59. Get unique customers list
- 60. How avoid accidental deletion?
- 61. What relation types exists in SQL?
- 62. Find non-Dollar/Euro countries
- 63. What is normalization in SQL?
- 64. What is a subquery?
- 65. Root categories list
- 66. Product List
- 67. Filtered list of products
- 68. Ten heaviest products
- 69. Determine the type of relationship
- 70. Sort the penguins
- 71. Find light weight penguins
- 72. Distribution penguins by islands
- 73. Find small penguins
- 74. Find small penguins species
- 75. Search by pattern
- 76. Flipper length to body mass rate
- 77. Penguins whose sex is unknown
- 78. Penguins with absent data
- 79. Heavy penguins
- 80. Count the penguins
- 81. Penguins and Islands
- 82. Medium sized bill Penguins
- 83. Penguins distribution
- 84. Common penguin species
- 85. Delete records
- 86. Delete employee records
- 87. Rename the table
- 88. Penguins stats
- 89. Create index
- 90. Create unique index
- 91. Drop table
- 92. Missing Data
- 93. Small bill Penguins
- 94. Not buyng customers
- 95. Penguins species
- 96. HAVING without aggregate
- 97. Duplicate Emails
- 98. Update Job Salaries
- 99. Create Penguins Table
- 100. Combine Penguin Lists
- 101. Unique Penguin List
- 102. Filter Little Penguins
- 103. Full-Text Index
- 104. Fare Conditions Types
- 1. Find customers full names
- 2. Find addresses using sub-query
- 3. Find addresses using JOIN
- 4. Find duplicate actor names
- 5. Most Popular Actor Surname
- 6. Find all the actors in the film
- 7. Find all films of an actor
- 8. Find the distribution of films by category
- 9. Find the average length of a movie by category
- 10. Find the number of films in which the actor took part
- 11. Find actors more popular than HENRY BERRY
- 12. Analyze monthly payment
- 13. Find the best month
- 14. Find the most popular film
- 15. Analyze rental data for film
- 16. Find the Managed Department
- 17. Employees on the Video Database Project
- 18. Find all customers with unshipped orders
- 19. Sort Movies by Multiple Fields
- 20. Get the longest movie
- 21. Get films list third page
- 22. Find the films never been rented
- 23. Find customers who did not return rented discs
- 24. Find average daily film rentals
- 25. Calculate daily income for the month
- 26. Generate dates table
- 27. Calculate the number of days off in a month
- 28. The average cost of renting a movie by category
- 29. Average film rental duration for each customer
- 30. Find Long Comedies
- 31. Find the distribution of customer activity
- 32. Find company stores details
- 33. Find clients who rented the film
- 34. Find minimum, maximum and average film duration
- 35. Find long movie categories
- 36. Find the number of employees
- 37. Find movie distribution by store
- 38. Find highly paid employees
- 39. Find employees by hire date
- 40. Get list of highly paid employees
- 41. Find valuable employees
- 42. Salary Ratio Calculation
- 43. Find the average client activity time
- 44. Find the average revenue
- 45. Find the store average revenue
- 46. Create a salary rating
- 47. Quarterly earnings analysis
- 48. Find the countries with the most customers
- 49. Find customer details
- 50. Find the count of rented disks
- 51. Find the number of returns
- 52. Find the least popular movies
- 53. Find the top spending customers
- 54. Films Without Available Inventory
- 55. Find languages not represented in films
- 56. Find movies that have never been rented
- 57. Clients with a high number of rentals
- 58. Films with the highest replacement cost
- 59. Count the rental delays
- 60. Calculate the percentage of delays
- 61. Get movie cast lists
- 62. Extract address and domain from email
- 63. Get a list of actors - namesakes
- 64. List Movies with Their Categories
- 65. Average Rental Duration
- 66. Movie rental prices by category
- 67. Get rolling payment amounts
- 68. Find the number of films in each category
- 69. Analyze customer payments
- 70. Find the most diverse clients
- 71. Find film distribution
- 72. Payments analyze
- 73. Get list of tables
- 74. Get table columns data
- 75. Get list of indexes
- 76. Distribution of clients by weekday
- 77. Client Rentals by Time of Day
- 78. Improve distribution of clients by weekday
- 79. Movies without actors records
- 80. Movies without cast records
- 81. Actors Excluding NC-17 Films
- 82. Average rentals count
- 83. Find repeat rentals
- 84. Find the distribution of clients by country
- 85. Find never been delayed films
- 86. Find most delayed films
- 87. Create department table
- 88. NC-17 Films about DBA
- 89. Films about Dogs or Cats
- 90. Clients whose first name is the last name of another client
- 91. Update Canadian postal codes
- 92. Enter Woodridge postal code
- 93. Enter new staff record
- 94. Create customer addresses view
- 95. Distribution of films by categories in JSON format
- 96. Generate a list of movies in JSON format
- 97. Category popularity analysis
- 98. Build a overal email list
- 99. Select clients without the letter "A"
- 100. Alter the staff table
- 101. Find films in several categories
- 102. Get a list of airports
- 103. Find Boeing aircraft
- 104. Get flights departed from Domodedovo
- 105. Get a list of aircraft from Domodedovo
- 106. Aircraft usage analysis
- 107. Find last names with double letters
- 108. Customers with matching name initials
- 109. Format Customer Names
- 110. Aircraft Lacking Business Class Seats
- 111. Get counts of seats by class
- 112. Get rows and seats count
- 113. Calculate the number of seats on flight
- 114. Get a list of airports with direct connections
- 115. Get a list of no-show passengers
- 116. Get a list of passengers
- 117. Find the flight delay
- 118. Get flights statistics
- 119. Find the fastest flight
- 120. Calculate the daily number of flights
- 121. Obtain a list of passengers
- 122. Get list of tables
- 123. Get information about the columns
- 124. Airports with one-way departures
- 125. Find airports relations
- 126. Get the passenger list
- 127. Get aircraft seat map
- 128. Get a list of planes in the air
- 129. What are DDL commands?
- 130. What are DML commands?
- 131. How is data stored in a relational database?
- 132. What is a constraint in SQL?
- 133. SQL constraints types
- 134. Choose tables join type
- 135. Calculate the tax
- 136. Get formatted list of films
- 137. What is a primary key?
- 138. Update customer's address
- 139. Adjust the rental cost
- 140. Find the tomorrow date
- 141. Start and End Dates of Current Month
- 142. Find the first and last dates of week
- 143. Display a table of airports
- 144. Count departing passengers
- 145. Number of passengers with total
- 146. Display a table of departures
- 147. Get a list of airports with more than one direct flight
- 148. Get list of tables (PostgreSQL)
- 149. A sub-departments list (JOIN)
- 150. Get a list of sub-departments
- 151. Get list of tables (SQL Server)
- 152. Find duplicate phone numbers
- 153. What is a covering index?
- 154. What is a view in SQL?
- 155. Move film between categories
- 156. Find a June 2005 hit
- 157. Get product category color counts
- 158. Find films to share
- 159. How to find common rows in SQL?
- 160. Jobs Without Specific Requirements
- 161. Orders shipped in the following month
- 162. Update project information
- 163. Top states by population
- 164. What is a materialized view?
- 165. Reduced salaries
- 166. List of categories
- 167. List of subcategories
- 168. What is a SQL transaction?
- 169. What is a correlated subquery?
- 170. Create a phonebook
- 171. Staff Availability Report
- 172. What is "PIVOT" in SQL?
- 173. Film distribution by category and store
- 174. Find highly paid employees
- 175. Find the hypotenuse of a triangle
- 176. Find the median booking amount
- 177. Find the median film's length
- 178. Prepare mailing list
- 179. Penguins list
- 180. Daily income by source
- 181. Index usage
- 182. Using a covering index
- 183. Island with the minimum penguins mass
- 184. The most populated island
- 185. Classic movies
- 186. Penguins stats table
- 187. Managed by Robert Nelson
- 188. Tables joining algorithms in SQL
- 189. Delete films records
- 190. Analyze bill length
- 191. Analyze flipper length
- 192. What is denormalization in RDB?
- 193. Create functional index
- 194. Staff information
- 195. Customers without orders
- 196. Overloaded employees
- 197. Rank Penguins by Body Mass
- 198. Who ordered red helmet?
- 199. Who ordered helmet?
- 200. What bought Jon Grande?
- 201. The most popular product
- 202. Products catalog
- 203. Mountain Bikes catalog
- 204. Products by category distribution
- 205. Large categories
- 206. Set Last Service Date
- 207. Refurbished machines
- 208. Data migration
- 209. Penguins with low body weight
- 210. Most Frequent Co-Purchase
- 211. Most popular products
- 212. Average Sales Delay
- 213. Distance between cities
- 214. Penguins habitat
- 215. Extract Geometry as Text
- 216. Manhattan's subway stations
- 217. Area of the Neighborhood
- 218. Area of the Neighborhood
- 219. Neighborhood Average Area
- 220. Extract Geometry as JSON
- 221. New York streets length
- 222. Rented Films
- 223. What is FULL-TEXT index?
- 224. Find Aircraft with All Fare Conditions
- 225. Is the index fit for query?
- 226. Is the index fit for queries?
- 1. Find most active customers
- 2. Find sad actors
- 3. Find the most diverse actors
- 4. Find all films where HENRY BERRY did not participate
- 5. Calculate factorial
- 6. Find average disk idle time
- 7. Find the distribution by categories
- 8. Analyze monthly payments (2)
- 9. Find movie popularity rating
- 10. Find EMILY DEE fans
- 11. Clients who are not familiar with EMILY DEE films
- 12. Disc rental and return statistics
- 13. Films with Low Rental Time
- 14. Find actors duets
- 15. Find films that were out of stock
- 16. Improve payments analyze
- 17. Find all the actors in the film
- 18. Analyze weekly rentals
- 19. Find the Horror films fans
- 20. Movies in One Store
- 21. Find movies with no available copies
- 22. Staff performance analysis
- 23. Generate monthly bill
- 24. Gap & Islands problem
- 25. Find customers who sow same films
- 26. Get the list of destination airports
- 27. Get a list of airports without direct connections
- 28. Rate airports
- 29. Find a list of flight options
- 30. Rental history
- 31. Find average flight occupancy
- 32. Find flight occupancy by fare
- 33. Find small airports
- 34. FInd the plane coordinates
- 35. FInd the planes coordinates
- 36. What is a subset of the SQL language?
- 37. SQL set operators
- 38. Find a 2005 hits
- 39. Analysis of the cost of film rental by category
- 40. Distribution of flights by days of the week
- 41. Subcategories count
- 42. Find the median salary
- 43. Actual statistics
- 44. Actual statistics 2
- 45. Cumulative payment analysis
- 46. Country Area
- 47. Population Distribution (Pivot)
- 48. Little Italy Stations
- 49. Frequently Purchased Product Pairs
Dear user,
You and many others enjoy this site for free. Our only sources of funding are advertising and donations. To help us keep this project free for everyone, please consider not blocking ads on the site.
If ads are an issue for you, please let me know, and I'll turn them off for your account.
You and many others enjoy this site for free. Our only sources of funding are advertising and donations. To help us keep this project free for everyone, please consider not blocking ads on the site.
If ads are an issue for you, please let me know, and I'll turn them off for your account.
Task
To keep the penguins_by_islands table (created in this task) up to date, create a trigger that fires after rows are deleted from the penguins table and reduces the number of penguins living on the corresponding island.
Use SQLite syntax to write your answer. Descriptions of tables are provided in the right pane.
Write your request in the field below and click the "Check it!" button.
Get hint
Copy code
Clear editor
Querynomicon (SQLite)
A compact database for learning the basics of SQL.The Querynomicon database tables:
department - table of departments.
- identDepartment ID
- nameDepartment name
- buildingBuilding name
| ident | name | building |
|---|---|---|
| gen | Genetics | Chesson |
little_penguins - table of little penguins.
- speciesPenguin species
- islandIsland of residence
- bill_length_mmBill length, mm
- bill_depth_mmBill depth, mm
- flipper_length_mmFlipper length, mm
- body_mass_gBody mass, g
- sexSex
| species | island | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g | sex |
|---|---|---|---|---|---|---|
| Gentoo | Biscoe | 52.1 | 17 | 230 | 5550 | MALE |
penguins - table of penguins.
- speciesPenguin species
- islandIsland of residence
- bill_length_mmBill length, mm
- bill_depth_mmBill depth, mm
- flipper_length_mmFlipper length, mm
- body_mass_gBody mass, g
- sexSex
| species | island | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g | sex |
|---|---|---|---|---|---|---|
| Gentoo | Biscoe | 52.1 | 17 | 230 | 5550 | MALE |
staff - table of employees.
- identEmployee number
- personalEmployee first name
- familyEmployee last name
- deptDepartment
- ageAge
| ident | personal | family | dept | age |
|---|---|---|---|---|
| 7 | Abram | Chokshi | gen | 23 |
machine - table of machines.
- identMachine ID
- nameMachine name
- detailsJSON with details
| ident | name | details |
|---|---|---|
| 1 | WY401 | {"acquired": "2023-05-01"} |
| 2 | Inphormex | {"acquired": "2021-07-15", "refurbished": "2023-10-22"} |
| 3 | AutoPlate 9000 | {"note": "needs software update"} |
