17. SQL functions and summaries
There are a whole load of functions that we can run. Let’s look at what happens when we run all of these functions:
SELECT * from genes; SELECT COUNT(*) FROM genes; SELECT MIN(start) FROM genes; SELECT MAX(start) FROM genes; SELECT SUM(start) FROM genes; SELECT AVG(start) FROM genes; SELECT ROUND(AVG(start)) FROM genes; SELECT UPPER(entity) FROM genes; SELECT LOWER(entity) FROM genes; SELECT LENGTH(entity) FROM genes; SELECT NOW(); SELECT MAX(start) FROM genes; SELECT entity, MAX(start) FROM genes GROUP BY entity;
Output:
example=# SELECT * from genes; pk | gene_id | entity | source | start | stop | sequencing_pk | ec_pk ----+---------+------------+--------+-------+------+---------------+------- 1 | Gene1 | Chromosome | ena | 190 | 255 | 1 | 1 2 | Gene2 | Chromosome | ena | 375 | 566 | 1 | 2 3 | Gene3 | Chromosome | ena | 780 | 980 | 1 | 1 4 | Gene4 | Plasmid | ena | 1001 | 1111 | 1 | 2 5 | Gene5 | Plasmid | ena | 786 | 888 | 1 | 2 (5 rows) example=# SELECT COUNT(*) FROM genes; count ------- 5 (1 row) example=# SELECT MIN(start) FROM genes; min ----- 190 (1 row) example=# SELECT MAX(start) FROM genes; max ------ 1001 (1 row) example=# SELECT SUM(start) FROM genes; sum ------ 3132 (1 row) example=# SELECT AVG(start) FROM genes; avg ---------------------- 626.4000000000000000 (1 row) example=# SELECT ROUND(AVG(start)) FROM genes; round ------- 626 (1 row) example=# SELECT UPPER(entity) FROM genes; upper ------------ CHROMOSOME CHROMOSOME CHROMOSOME PLASMID PLASMID (5 rows) example=# SELECT LOWER(entity) FROM genes; lower ------------ chromosome chromosome chromosome plasmid plasmid (5 rows) example=# SELECT LENGTH(entity) FROM genes; length -------- 10 10 10 7 7 (5 rows) example=# SELECT NOW(); now ------------------------------- 2021-10-27 12:06:02.175913+01 (1 row) example=# SELECT MAX(start) FROM genes; max ------ 1001 (1 row) example=# SELECT entity, MAX(start) FROM genes GROUP BY entity; entity | max ------------+------ Plasmid | 1001 Chromosome | 780 (2 rows)
Wednesday 27 October 2021, 968 views
Next post: 18. Altering the database Previous post: 16. SQL Query performance
Advanced Web Development index
- 38. Writing API tests
- 37. Testing in Django
- 36. Class-based views in the Django REST framework
- 35. Building a RESTful web service in Django
- 34. Introduction to CRUD, REST and APIs
- 33. Refactoring with generic views in Django
- 32. Django validators
- 31. Django forms (2) – using the ModelForm class
- 30. Django forms (1)
- 29. JavaScript basics
- 28. Adding CSS to the template
- 27. Django templating
- 26. Deleting and updating records
- 25. Joins, filters and chaining commands
- 24. Using the ORM in views.py
- 23. Adding to the database by writing a script
- 22. Adding to the database with Django Admin
- 21. Migrations
- 20. ORM – work through example
- 19. An introduction to the Object-Relational Mapper
- 18. Altering the database
- 17. SQL functions and summaries
- 16. SQL Query performance
- 15. Queries and table joins in SQL
- 14. Inserts and queries in SQL
- 13. Good practice in relational database design
- 12. Limitations to database modelling
- 11. Building a database using SQL
- 10. Introduction to PostgreSQL
- 9. How to start writing a new application in Django
- 8. Building a lightweight project
- 7. Django URLs
- 6. Django templates
- 5. Django models
- 4. Django views
- 3. Creating a new hello app
- 2. Creating a new virtual environment
- 1. Setting up Django
Leave a Reply