Flashcards
Refining selections
Cards in this deck (29)
- 1How to have all rows where a property will only have different results ?In this case, also using the birth date and first name in conjunction will ensure that we don't miss different persons with the same last name.
- 2How to order rows by one characteristics ?By default, the order is ASC (= in alphabetical order), but you can put DESC.
They would be selected by order they were inserted if there were no ORDER BY.
- 3How to order rows by many characteristicsHere, the 2 corresponds to author_fname.
We can put as many column names we want after ORDER BY.
- 4How to only have 3 results from a search ?
- 5How do we get all rows ordered by from the 95th one ?
- 6How to look for all rows with "da" in them ?The % mean anything or nothing can be before or after "da".
- 7How to find all rows with four characters ?
- 8How to know how many raws there is in the DB ?
- 9How to get the smallest value of a column ?
- 10What if I want the title of the longest book ? title, pages (books)
- 11Having the year of the first publication from each author. fname, lname, released-Y (books)
- 12Sum all pages in the entire DB pages
- 13Sum all pages each author has written fname, lname, pages (books)SELECT author_fname, author_lname, SUM(pages) FROM books
GROUP BY author_fname, author_lname; - 14Difference between CHAR & VARCHARCHAR has a fixed length, it’s faster for fixed length text. For exemple, if we put char(5) for the titles, they will be truncated if they’re longer than that. If it’s shorter, spaces are added to make it 5.
- 15Explain Decimal
- 16Difference Float/Double and DecimalFLOAT & DOUBLE store larger numbers using less space at cost of precision.
- 17Explain DATE, TIME & DATETIME
- 18Explain CURDATE, CURTIME & NOW
- 19Not Equal!=
- 20Not LikeNOT LIKE ''
- 21Greater Than Greater or equal to>
>= - 22What returns SELECT 99 > 11, l'équivalent de TRUE
- 23What returns 'A' > 'a'False
But >= would be true - 24Less than Less or equal to<
<= - 25AND OR&&
|| - 26We want the books released from 2004 to 2015
- 27When we want to make operations/comparaisons on different datatypes we :USE CAST() to explicitly convert the values of the desired data type (when you use strings and dates, different datatypes …)
- 28How can we do the same, but shorter ?
- 29Instead of having a date when we ask for the release_date, how do we get "Modern" for after/in 2000 and Old for before 2000 ?
Information
- Description
- vaze SQL Flashcards on Refining selections, created by dadou siplancin on 14/02/2022.
- Created by
- dadou siplancin
- Views
- 0
- Rating