SQL code copied to buffer
Group tasks by:
Support the project.
- 1. What is a Database?
- 2. What is DBMS?
- 3. What is RDBMS?
- 4. How is data stored in a relational database?
- 5. What is ACID?
- 6. What is SQL?
- 7. What is a subset of the SQL language?
- 8. What are DDL commands?
- 9. What are DQL commands?
- 10. What are DML commands?
- 11. What is index in SQL?
- 12. Index usage
- 13. Is the index fit for query?
- 14. Is the index fit for queries?
- 15. What is a covering index?
- 16. Using a covering index
- 17. What is a constraint in SQL?
- 18. What is a primary key?
- 19. SQL constraints types
- 20. SQL Tables joins types
- 21. Choose join type
- 22. Choose tables join type
- 23. Tables joining algorithms in SQL
- 24. Order of execution of logical operators
- 25. SQL set operators
- 26. Difference between UNION and UNION ALL
- 27. How to find common rows in SQL?
- 28. What relation types exists in SQL?
- 29. Determine the type of relationship
- 30. What is a view in SQL?
- 31. What is a materialized view?
- 32. How avoid accidental deletion?
- 33. What is a SQL transaction?
- 34. What is normalization in SQL?
- 35. What is denormalization in RDB?
- 36. What is a subquery?
- 37. What is a correlated subquery?
- 38. What is "PIVOT" in SQL?
- 39. HAVING without aggregate
- 40. What is FULL-TEXT index?
- 1. Get the actors
- 2. Sort the penguins
- 3. Addresses Lacking Postal Codes
- 4. Ordered Languages List
- 5. Retrieve Actor Names
- 6. Get the languages list
- 7. Ordered Movie Titles
- 8. Get list of clients
- 9. Unique Movie Ratings
- 10. Top 5 Longest Films
- 11. Top 10 Movies by Title
- 12. Get films list third page
- 13. Sort Movies by Multiple Fields
- 14. Get the longest movie
- 15. Find long movies
- 16. Find stuff members by condition
- 17. Find active customers
- 18. Retrieve Actors by Name
- 19. Find film names by description
- 20. Get the sorted list of films with condition
- 21. Find Long Comedies
- 22. Select clients without the letter "A"
- 23. NC-17 Films about DBA
- 24. Find films about Dogs or Cats
- 25. Get list of restricted films
- 26. Get the restricted films list
- 27. Employees on the Video Database Project
- 28. Find foreign employees
- 29. Find employees by hire date
- 30. Find films with no available inventory
- 31. Find languages not represented in films
- 32. List Movies with Their Categories
- 33. Extract address and domain from email
- 34. Get table columns data
- 35. Get list of indexes
- 36. Movies without cast records
- 37. Clients whose first name is the last name of another client
- 38. Find clients who have met each other
- 39. Find movies that have never been rented
- 40. Find films in several categories
- 41. Customers with matching name initials
- 42. Rental history
- 43. Rented Films
- 1. Calculate circle perimeter
- 2. Calculate the area of a circle
- 3. Find the hypotenuse of a triangle
- 4. Calculate factorial
- 5. Generate a list of movies in JSON format
- 6. Addresses with Even Postal Codes
- 7. Build a overal email list
- 8. Generate monthly bill
- 9. Build shared surnames list
- 10. Identify Palindrome Names
- 11. Format Customer Names
- 12. Calculate the tax
- 13. Get formatted list of films
- 14. Find the tomorrow date
- 15. Start and End Dates of Current Month
- 16. Find the first and last dates of week
- 1. Find the average length of a movie
- 2. Find the minimal and maximal film rental cost
- 3. Average Rental Duration
- 4. Find the number of employees
- 5. Find the number of films in each category
- 6. The average cost of renting a movie by category
- 7. Find minimum, maximum and average film duration
- 8. Find long movie categories
- 9. Find the least popular movies
- 10. Find the top spending customers
- 11. Average film rental duration for each customer
- 12. Analyze monthly payment
- 13. Find movie distribution by store
- 14. Find valuable employees
- 15. Salary Ratio Calculation
- 16. Quarterly earnings analysis
- 17. Find the countries with the most customers
- 18. Find the count of rented disks
- 19. Find the number of returns
- 20. Get a list of actors - namesakes
- 21. Get movie cast lists
- 22. Find all the actors in the film
- 23. Analyze weekly rentals
- 24. Find repeat rentals
- 25. Movies in One Store
- 26. Find movies with no available copies
- 27. Distribution of films by categories in JSON format
- 28. Find a June 2005 hit
- 29. Find a 2005 hits
- 30. Analysis of the cost of film rental by category
- 1. Find addresses using sub-query
- 2. Clients who are not familiar with EMILY DEE films
- 3. Movies with the highest replacement cost
- 4. Movies with above-average rental rates
- 5. Clients with a high number of rentals
- 6. Films with Low Rental Time
- 7. Movies without actors records
- 8. Actors Excluding NC-17 Films
- 1. Movie rental prices by category
- 2. Get rolling payment amounts
- 3. Find average disk idle time
- 4. Find the distribution by categories
- 5. Get list of highly paid employees
- 6. Create a salary rating
- 7. Find movie popularity rating
- 8. Find customer details
- 9. Find EMILY DEE fans
- 10. Films with the highest replacement cost
- 11. Find the Horror films fans
- 1. Find the average client activity time
- 2. Find the average revenue
- 3. Find the store average revenue
- 4. Analyze customer payments
- 5. Analyze monthly payment
- 6. Analyze monthly payments (2)
- 7. Find movie popularity rating
- 8. Find the count of rented disks
- 9. Find the number of returns
- 10. Disc rental and return statistics
- 11. Count the rental delays
- 12. Calculate the percentage of delays
- 13. Find the most diverse clients
- 14. Daily income by source
- 15. Find actors duets
- 16. Find film distribution
- 17. Find films that were out of stock
- 18. Payments analyze
- 19. Improve payments analyze
- 20. Distribution of clients by weekday
- 21. Improve distribution of clients by weekday
- 22. Client Rentals by Time of Day
- 23. Find never been delayed films
- 24. Find most delayed films
- 25. Staff performance analysis
- 26. Category popularity analysis
- 27. Gap & Islands problem
- 28. Find customers who sow same films
- 29. Get a list of no-show passengers
- 30. Find average flight occupancy
- 31. Find flight occupancy by fare
- 32. Find the median salary
- 33. Find the median booking amount
- 34. Find the median film's length
- 35. Analyze bill length
- 36. Analyze flipper length
- 37. Most Frequent Co-Purchase
- 38. Most popular products
- 39. Not buyng customers
- 40. Average Sales Delay
- 41. Frequently Purchased Product Pairs
- 42. Sales by Category Percentage
- 43. Product Sales Analysis
- 1. Create new address record
- 2. Update the postal code
- 3. Enter Woodridge postal code
- 4. Update Canadian postal codes
- 5. Enter new staff record
- 6. Create customer addresses view
- 7. Penguins distribution
- 8. Remove customer records
- 9. Create department table
- 10. Alter the staff table
- 11. Perform price update
- 12. Update customer's address
- 13. Adjust the rental cost
- 14. Update replacement cost
- 15. Move film between categories
- 16. Penguins stats table
- 17. Actual statistics
- 18. Actual statistics 2
- 19. Delete records
- 20. Delete employee records
- 21. Delete films records
- 22. Rename the table
- 23. Penguins stats
- 24. Create index
- 25. Create unique index
- 26. Drop table
- 27. Create functional index
- 28. Full-Text Index
- 29. Create Penguins Table
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.
For all penguins in the little_penguins table, get the species, island, flipper length, maximal flipper length in the table, and the difference in individual flipper length from the maximum. Build a result table with the fields species, island, flipper_length_mm, max_flipper_length_mm, diff_from_max_flipper_length_mm. Solve the problem without window functions.
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"} |
