4 Create a table named SCRIPTS CREATE TABLE scripts( aColumn TYPE );A table must have at least 1 column.
Also, the column data type must be specified 5 Make a table named SCRIPTS being sure it is NOT present yet CREATE TABLE IF NOT EXISTS scripts (aColumn TYPE);A table must have at least 1 column.
Also, the column data type must be specified 8 Introduce a new table named FOO2 identical to an already existing table named FOO1 (then read tip) CREATE TABLE foo2 SELECT * FROM foo1;This procedure creates a table identical and ALSO populated with all the entries of the foo1 table.
If you want to create an identical table without importing all the entries, select only one record from the foo1, then delete it from foo2. 9 Arrange a temporary table definition named SCRIPTS getting all the fields present in another table named OLDS CREATE TEMPORARY TABLE scripts SELECT * FROM olds;Creating a table scheme from ANOTHER table means NOT only to assign to the newly generated table the field NAMES named after the names the fields had in the already existing table, but also assigning to these fields the VALUES already present in the existing one, thus actually populating the newly generated table with active records. 10 Arrange a temporary table definition named SCRIPTS made of two fields named F1 and F2 present in another table named OLDS CREATE TEMPORARY TABLE scripts SELECT f1, f2 FROM olds;See the tip in 9 11 Arrange a temporary table definition named SCRIPTS but only if it is not present yet, and if so made it of two fields named F1 and F2 drawn from another table named OLDS CREATE TEMPORARY TABLE IF NOT EXISTS scripts SELECT f1, f2 FROM olds;Note IF NOT EXISTS goes soon after the keyword TABLE but BEFORE the name of the table: this is because upon creation, soon after the name of a table must somewhat logically go only the instructions for the fields it contains. 13 Arrange a temporary table definition named SCRIPTS made of 2 fields named F1 and F2 both allowing for a choice between two values: either 'yes' or 'no' CREATE TEMPORARY TABLE scripts ( f1 ENUM('Yes', 'No'), f2 ENUM('Yes', 'No') );The trick is a small exercise on the data type named ENUM: it wants parenthesis, if the values are strings they must be in between quotes, and is a way to allow only for values included among the given ones.
It is highly unadvisable that you add extra or trailing whitespaces INSIDE the commas containing the values chosen as enumerable: extra trailing white space/s for instance would make of a:
'no' a 'no '
A script would consider them DIFFERENT!
If you need an enumeration that allows for MORE than one choice, use SET('valid1', 'valid2') - the amount of elements can be up to 65,535 in enum and up to 64 in set. The difference between ENUM and SET is this: ENUM you can pick only one in the list, SET you can pick how many as you prefer as long as in the list. 14 Make a table definition named scripts; be sure its first field named f1 is a string of up to 15 characters and that its standard value is the string: Hallo CREATE TABLE scripts( f1 VARCHAR(15) DEFAULT 'Hallo' );3 tips:
1: if you set varchars to a given length and you give it a default, be sure its default is a string AND does not exceed the provided length!
2: remember: strings go in between quotes!
3: keyword DEFAULT doesn't want the sign equal (=) like in programming languages an assignment would! 16 Invent a table definition named SCRIPTS only if it doesn't exists yet; make in it 2 fields named FUNCTION and ARGUMENTS.
The first must be a varchar of max 255 letters and a primary key as well; the second an integer whose standard value is 0 CREATE TABLE scripts ( f1 VARCHAR(255) PRIMARY KEY, f2 int DEFAULT=0 );The name of a PRIMARY KEY field CANNOT be the word... key!
Keyword DEFAULT doesn't want the sign equal (=) like in programming languages an assignment would.
Please do not specify a limit of digits for the INT in this example; you could have, actually: instance: int(10). 17 Produce a table definition named SCRIPTS if it doesn't exists yet. Make in it 4 fields:
the first is named MYKEY and is a primary key and must start from number 100 and augments itself by one;
the second is called NAME and contains names of persons,
the third is named PREFIX and contains a smallint,
the fourth is named PHONE, is an integer, and must have no possible copies (...namely let's pretend phone numbers cannot be shared!) CREATE TABLE IF NOT EXISTS scripts (
key INT PRIMARY KEY DEFAULT 100 AUTO_INCREMENT,
name VARCHAR(40),
prefix SMALLINT,
phone INT UNIQUE);4 important things:
The constraint AUTO_INCREMENT can be set only on one field in a table, and such field must be a KEY: key fields are those labeled by previous constraints as UNIQUE or PRIMARY KEY.
Also, you cannot set an AUTO_INCREMENT if you've not declared the field has to carry some numeric DATA TYPE, typically INT. For a primary key field which is numeric, it is advisable to set an auto_increment feature as well.
Although you can have many fields labeled as UNIQUE, you can have only one labeled as PRIMARY KEY in one table.
The name of a PRIMARY KEY field CANNOT be the word... key! 18 Does it make sense to include unique values or primary keys in tables meant to implement a many-to-many relation between two other tables?
YES or NO? NOA many-to-many table can certainly include the primary key taken from another table to link it with a primary key taken from a second table, but NONE of these keys should be set as a primary key to THIS THIRD TABLE: in fact a primary key must have NO copies in a table, whereas such third tables must typically allow for MULTIPLE matches from table one to table two and vice-versa! 19 When you make a new table and you insert a field meant to be primary key and its data type is set to INT or any other type, what is the order to declare these constraints?
1=primary key goes BEFORE data type
2=primary key goes AFTER data type
3=doesn't matter. 1Data type soon after the name of the field and the rest after it, or you'd get an Error! 20 Are key and primary key the same thing? Yes/No NoA primary key doesn't allow duplicate values, whereas a KEY is a hidden indexing field that gets populated in background by autonomous values, and that the inner engine of mySQL would use to index the entries without you're aware of it. Therefore also a key doesn't allow duplicates BUT it is not up to you to bother with that: it is a hidden field. 21 Modify the name of a table named SCRIPT into SCRIPTS ALTER TABLE script RENAME scripts;When you modify a table, you can NOT use commands like IF EXISTS, strangely enough.
Also, and still strangely enough, the command to change a table name RENAME is a different keyword than the command used to change a field (cell) name: in fact in the latter case you use keyword CHANGE 22 In the definition of a table named SCRIPTS, modify the name of a text field named F1 into the new name F2 ALTER TABLE tab CHANGE f1 f2 VARCHAR(2);Whenever you rename a field you must also SPECIFY its (new?) DATA TYPE.
You can NOT use parenthesis after keyword CHANGE to include the fields, strangely enough: as a remarkable consequence, you cannot but change ONE field at a time, apparently.
The form just asks you to specify some type, no matter WHICH. Try. 24 In the definition of a table named SCRIPTS, modify:
the name of a text field named F1 into the new name F2, and make its new standard value equal to zero. ALTER TABLE tab CHANGE f1 f2 INT DEFAULT 0;You must ALWAYS specify the data type.
If you assign also a default, the default must be of the specified data type!
Also, assigning a DEFAULT is the best way to avoid being assigned the 'value' NULL to a field left empty. The move to reset the default of a column DOES NOT AFFECT THOSE records that have already been filled with values different by the default, which includes NULL. 25 In the definition of a table named SCRIPTS change the data TYPE of a field named F1 into the integer data type. ALTER TABLE scripts CHANGE f1 f1 INT;The trick to change only the data type is to assign as the field name to be changed the name of the field itself, and then change only its type. 26 In the definition of a table named SCRIPTS transform a field named F1 into the primary key field. ALTER TABLE scripts CHANGE f1 f1 VARCHAR(30) PRIMARY KEY;You can set a primary key field ONLY if one is not already present. You should drop the existing one first.
A table which carries two fields both holding identical values cannot get an altered field into a primary key
The NAME of the field must be the same, repeated twice therefore, since the request was to change a field ATTRIBUTE (so called SQL constraint) WITHOUT changing its name.
I repeat: when transforming a field into PRIMARY KEY, be sure the existing records do NOT have two or more identical VALUES stored in that field instances, or SQL would refuse assigning the field a primary key nature: even empty fields would be regarded as carrying identical... no-no-values!
ALWAYS specify a data type. 27 In the definition of a table named SCRIPTS eliminate the primary key nature of a field ALTER TABLE scripts DROP primary key;The syntax DROP primary key eliminates from the field that is the primary key field its nature of primary key storehouse (thus letting you, for instance, define an entirely new primary key field) but remember that it will NOT eliminate the field nor its values: it ONLY changes its status of primary key storehouse field! 28 Can you add a KEY by keyword KEY (not by primary key) upon creating a table? Yes/No YesYes. A KEY is just an index that you instruct to be created in background, and as such you can BOTH ADD it after a table has already been CREATED, or UPON creating it. 29 Create a table named FOO.
Its first field is named f1, then add upon creation also a key field named fkey1 that indexes f1 CREATE TABLE foo (f1 int(3), KEY fkey1(f1));Unfortunately, keyword KEY when initializing a key field goes BEFORE the name of the key field. KEEP IN MIND this strange fact matches the strange reality a key field is actually 'inivible' and merely indexes a field in background! 30 Include a key to a table named FOO such that it indexes one field named f1 ALTER TABLE foo ADD KEY aname(f1);Also:
ALTER TABLE foo ADD INDEX aname(f1) 31 Include a key to an already existing table named FOO such that it indexes two fields named f1 and f2 ALTER TABLE foo ADD KEY aname(f1,f2);Also:
ALTER TABLE foo ADD INDEX aname(f1,f2) 32 Include a key to a table named FOO such that it indexes two fields named f1 and f2 and such as both fields get indexed by taking in only the first 6 chars ALTER TABLE foo ADD KEY aname( f1(6) ,f 2(6) );Also:
ALTER TABLE foo ADD INDEX aname( f1(6) , f2(6) ) 33 Delete a key onto a table named FOO that you know you called ANAME ALTER TABLE foo DROP KEY aname;Also:
ALTER TABLE foo DROP INDEX aname 34 Add a primary key field named p1 onto an ALREADY created table named FOO CREATE primary key ON TABLE foo;Also:
ALTER TABLE foo DROP INDEX aname 35 In the definition of a table named SCRIPTS include a new field named FX meant to carry 10 characters (no more, no less!) ALTER TABLE scripts ADD fx CHAR(10);To set a very strict (rigid!) amount of chars within the 255 bytes limit, use data type CHAR instead of VARCHAR: char means only 10, varchar would mean UP TO 10. 36 In the definition of a table named SCRIPTS delete a column named F1 ALTER TABLE scripts DROP f1;If the column is the LAST field left, you cannot drop it; drop the table instead. 37 A small leap forward to avoid being too predictable: Solve this problem with a SQL query:
you have one table named FOO.
In such table there are only 2 fields named NAME and SURNAME.
Alas, by mistake you have inserted twice the SAME record: two entries carry the same name 'John' and the same surname 'Smith' (we pretend such values cannot belong to two different persons, ok?).
You now create a temporary table, that isolates (contains) all the fields values but drawn only from ONE of those twin records...
(about why this move is important, it is described in the TIP field) CREATE TEMPORARY TABLE aName
SELECT DISTINCT * FROM foo
WHERE name='John' AND surname='Smith';All the trick is in the use of the keyword DISTINCT, and in the storage into a TEMPORARY table.
Moreover, consider that there is no reason to keep in an original table records whose ALL fields are IDENTICAL in values: they aren't but the very same thing!
Thence the utility to keep in mind a trick to isolate one of such twin records with the subsequent intention to destroy them all in the original table and then reinsert in the main table, from the temporary table, ONLY ONE of them!
You may want to consider to do this without a temporary table but a foo foo table, safer in case you're dealing with critical data. 38 Given a table named FOO including 3 fields, add a record with all the fields specified (no matter what type of value you choose, do not focus on that) INSERT INTO tab VALUES('text or num', 'text or num', 'text or num');The keyword INTO is actually optional.
When you do not specify the fields to populate upon insertions, it is assumed you're going to populate them all || get an error! 39 Given a table named FOO including 3 fields, the first a numerical primary key the next 2 strings, include a new record with all the first three fields specified INSERT INTO tab VALUES(0000, 'text or num', 'text or num');A primary key cannot be a duplicate of an already existing primary key. Also, if the primary key wants a NUMBER, an attempt to include a STRING would generate an error claiming the primary key already exists (duplicated as... zero!) 42 Given a table named FOO including 3 fields named f1 f2 and f3:
include a new record where only fields f2 and f3 are specified,
AND they get drawn from fields named x1 and x2 in ANOTHER table named FOO2,
and get such fields from such table only where the value of another field named x3 in this latter table carries a value equal to: Smith. INSERT INTO tab(f2,f3) SELECT x1,x2 FROM tab2 WHERE x1='Smith';Don't forget to put strings in between apex 47 If in a table with a primary key you insert a record without defining the value meant for its field, and such primary key is also set to be an auto_increment field, would you get an error? yes/no
After your answer possibly read the tip too. No - perhaps you want to see tip tooThe field would get filled with the HIGHEST value found for the primary key, AUGMENTED by one. If no previous record is found, the primary key gets initialized as number 1. 48 If in a table with an UNIQUE labeled field (namely not necessarily a primary key) you insert a record with a duplicate value for the unique field, would you get an error? yes/no
After your answer possibly read the tip too. Yes - perhaps you want to see tip tooThe field must carry different values. You cannot set more than one (!) UNIQUE labeled field to auto_increment, and this ONLY if there is no primary key as well set as auto_increment.
Anyway, if instead of INSERT you use REPLACE -same syntax as INSERT as for the rest- to insert a new record carrying a possibly duplicated value for some unique labeled field, this latest record would OVERWRITE the OLD one ENTIRELY, thus generating NO errors!
By the way an Auto_Increment field may apparently skip one digit in such process (the replaced entry transmits its last value before vanishing: if that was the highest, the auto_increment process grabs it before dropping it, thus generating a possible gap in the evenness of the auto_incrementing: say from 1000 to 1002 if the dropped/replaced entry carried, say, 1001). 49 Eliminate all the records from a table named FOO
(then read the tip) DELETE FROM foo;A syntax such as DELETE * FROM would even be considered wrong, unconsistently enough (no wildcard * symbol, that is).
The reason for this is that actually it is conceptually different the idea of removing one whole Record/Row (DELETE) and that of removing one cell (ALTER - DROP) and that of updating a field (UPDATE). The idea of radical deletion should not be confused or tampering with that of alteration. 53 In a table named FOO you've made several deletions. Streamline/normalize it to be sure nothing lingers around, you see. OPTIMIZE TABLE foo;Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space. OPTIMIZE TABLE works by making a temporary copy of the original table; The old table is copied to the new table (without the unused rows), then the original table is deleted and the new one is renamed. While optimization is executing, the original table is readable by other clients, but updates and writes to the table are STALLED until the optimization is completed. No failed updates ensue.
This would not affect primary key or keys or indexes: Key fields are HIDDEN fields! unless you do not DROP their nature of key fields perhaps to bestow it back at a second moment, the deleted entries that were also indexed in the hidden key fields would not regain the positions in the key hidden fields: this means a key stopped at number say 7, and that undergoes a deletion of the record indexed by 7, and the subsequent assignment of a new record would not assign 7 again, but would go on indexing by...8 onward!
Key fields are HIDDEN fields, they follow their own independent course! 54 When on mySQL you use the GRANT command, which database NAME the GRANT command automatically refers to? mysqlThis is why you can use grant in a script without being sure you're using the database named mysql (I assume you know that mySQL contains a default database named just... mysql!). 56 Authorize a co-administrator named Jim and whose password is 'hallo' to use all the prerogatives on the DB table inside the MYSQL database GRANT ALL ON mysql.db TO 'Jim' IDENTIFIED BY 'hallo';Keyword ON always wants after it a database name (or the general wildcards *.* meaning implicitly 'all' namely all databases): TABLES furtherly belonging to such database must be appended with a DOT.
Learn to discriminate between tables and databases the tables belong to! 57 Authorize a guy named Jim with password 'hallo' to do all he wants inside a database named JIM_DB GRANT ALL ON jim_db.* TO 'Jim' IDENTIFIED BY 'hallo';Crucial: if you authorize all on a database, remember that you STILL have to append after it a dot and then a wildcard to signify ALL THE TABLES inside it; would you just include the name of the database without the dot and * signs, mysql would assume it is not a database name but the name of a TABLE belonging to the mysql DATABASE! 58 IMPORTANT: Authorize a guy named Jim from whatever DOMAIN and with password 'hallo' to do all he wants inside a database named JIM_DB GRANT ALL ON jim_db.* TO Jim@'%' IDENTIFIED BY 'hallo';Remember that regardless some books omit this, the name@'%' syntax does REQUIRES QUOTES wrapping the % sign! For the use of .* see the note for the NEXT exercise. 59 Authorize a guy named Jim with password 'hallo' to read and update and delete only inside a database named JIM_DB (then read note) GRANT SELECT,UPDATE,DELETE ON jim_db.* TO 'Jim' IDENTIFIED BY 'hallo';In the particular case you're granting SPECIFIC options, you DO have to specify the TABLES too: specified privileges can apply only to TABLES; so if they are meant to affect ALL the tables, you STILL have to specify it by .* 60 Authorize a guy named Jim with password 'hallo' to read and update and delete only from a table named ATAB which is inside a database named JIM_DB GRANT SELECT,UPDATE,DELETE ON jim_db.atab TO 'Jim' IDENTIFIED BY 'hallo';You can authorize simultaneous rights on a specific table by separating them with a comma. 61 Authorize a guy named Jim with password 'hallo' to read only
1] from three columns named name,surname,phone
2] that belong to a table named ATAB
3] which is inside a database named JIM_DB GRANT SELECT (name,surname,phone) ON jim_db.atab TO 'Jim' IDENTIFIED BY 'hallo';For mysterious reasons, you cannot go on linking the column names with a dot but you have to put them before keyword ON and must be in between parenthesis. ALSO note this: if you specify SPECIFIC columns you grant rights on, the columns go in between brackets and separated by commas soon after the granted right. SUCH GRANTED right can, apparently, be only one: thus to grant further different rights on other columns (even on the same table) you have to issue one specific query like the one above changing SELECT with the new right (say: UPDATE) and then naming the relative columns in the fore mentioned fashion. 62 Withdraw all the privileges on the database named JIM_DB from the user named jim who connects from whatever remoter host (then read note). REVOKE ALL ON *.* FROM jim@'%';The user may persist in the USER table: delete it also by DELETE FROM.
Also, any specific privileges must be revoked SPECIFICALLY. 64 Withdraw the right to UPDATE on the columns named COL1 and COL2 from the table named TABLE in the database named DB from the user named jim connecting from everywhere REVOKE UPDATE (col1, col2) ON db.table FROM jim@'%';Specifying the columns to revoke on, you have to put them in between parenthesis and separating them, if more than one, with a comma. Revoking MORE privileges from other -or even the same- COLUMNS requires a single query as above for each privilege (DELETE, SELECT...). 65 Remove from the table USER in the mysql database the user whose name is jim and whose password is 'hallo' DELETE FROM mysql.user WHERE user='jim' AND password=password('hallo');When you GRANT, the password is AUTOMATICALLY crypt; when you select, you have to crypt it by password() to clutch the right match. 69 Update in a table named TAB the values of the field named f1 to the value: 'john' for that fields f1 whose current value is 'mary' BUT only if the table is not under some selection process. UPDATE LOW_PRIORITY tab SET f1='john' WHERE f1='mary';When you INSERT a new record, the keyword to postpone the operation is DELAYED.
When you UPDATE it is LOW_PRIORITY 72 What cities in field named CITIES are represented on table TAB? (see tip too) SELECT DISTINCT cities FROM tab;Use DISTINCT in order to see cities listed only ONCE! 73 What COUNTRY(es) our customers are - on table TAB? SELECT DISTINCT country FROM tab;See tip above. 77 Get COURSES and STUDENTS on TAB and show them in inverse alphabetical order basing upon STUDENT SELECT courses, students FROM tab ORDER BY students DESC;ORDER BY has been requested on the SECOND field (students) 78 Get COURSES and NAMES and SURNAMES on TAB and show them in alphabetical order firstly by SURNAME and then by NAME SELECT courses, names, surnames FROM tab ORDER BY surnames, names;Check your names carefully for any typo if you get an error!
ORDER BY has been requested to follow two specific fields in a specific order 79 Get COURSES and NAMES and SURNAMES on TAB and show them in alphabetical order firstly INVERSE by SURNAME and then ASCENDANT by NAME SELECT courses, names, surnames FROM tab ORDER BY surnames DESC, names ASC;You could even omit ASC for ASC (but NOT DESC!) is the default behaviour when ordering by.
Again: check your names carefully for any typo if you get an error!
ORDER BY has been requested to follow two specific fields in a specific order 80 ON table TAB get the ITEM and after the PRICEs and list them from the higher to the lower SELECT item, prices FROM tab ORDER BY prices DESC;DESC means that DESCends: consequently if you order by that, the higher values are to be on TOP and downhill from there. Since the DEFAULT of ORDER BY is ASC, including neither ASC or DESC would list on top the lowes values (it is rational: you arguably want on top by default records 0,1,2,3... namely and potentially the oldest!) 81 On table TAB: given ARRIVAL and ORDER fields which are dates of shipments, get for each ORDERID how many days elapse between ARRIVAL and ORDER SELECT orderId, arrival-order AS someName FROM tab;It is supposed fields arrival,order are DATE types: dates can be subtracted with a mere - operator: SQL takes care of that.
WHENEVER YOU PERFORM an operation thus yielding some result which is NOT in the fields of the table, you do have to assign to such newly generated result a NEW name by keyword AS nameHere.
Such new name can be of your choice. 82 On table TAB get CONTRACT and for each of them calculate your fee on field PRICE knowing your fee is 10% (namely =0.1) SELECT contract, price*0.1 AS someName FROM tabSee tip above. 83 On table TAB get CLERK and SALARY and see how much each new salary would be if you add to it 5% (namely =0.5) SELECT clerk, salary+salary*0.5 AS someName FROM tabYeah, a calculation as salary+salary*0.5 is correct: note that you want to know the whole salary once ADDED 5% to ITSELF, thencefore such calculation. IN this case the sum is performed AFTER the multiplication for the * mathmatical operator has a precedence on every programming lnaguages and this includes SQL too.
Note that if you have to perform calculations where such DEFAULT order has NOT to be respected, you have to parenthesize your elements. 84 On TAB get NAME, SURNAME in fields whose surname is 'smith' SELECT name, surname FROM tab WHERE surname='smith'Don't forget the apexes: 'smith' 86 On TAB list all PHONES and SURNAMES excluding those whose NATION is 'france' SELECT phones, surnames FROM tab WHERE nation <> 'france'The <> means WHERE IS NOT, and allows NO SPACES between the <> symbols, ok? 87 On TAB get the CITY of an order for those records where the SHIPMENT date is (arguably by mistake) set as minor than the ORDER date (yes, all such fields are supposed being of the DATE type) SELECT city FROM tab WHERE shipment < orderIt is supposed shipment and order are date types 88 On TAB get the CITY of an order for those records where the SHIPMENT date is higher or equal to today (let's assume today is 2000 january 28) SELECT city FROM tab WHERE shipment>='2000-01-28'It uses >= and a date format; date is plaintext so must be in between apexes! 89 On TAB find SURNAME and NAME of clerks hired (field: HIRED) on july 2000 (note: july has 31 days!!) and order in normal alphabetical order by surname first and by name after SELECT surname, name FROM tab WHERE hired BETWEEN '2000-07-01' AND '2000-07-31' ORDER BY surname, name;When you WRITE down a full date as such, it must be IN APEXES and in the right format. BETWEEN is range inclusive and to include both ranges you use it with AND too. 90 On TAB get the SURNAME of guys whose surname is under letter B SELECT surname FROM tab WHERE surname BETWEEN 'b' AND 'bz';Being BETWEEN range INCLUSIVE of extremes, a condition that starts with 'b' and ends with 'bz' includes all that is among Bs...
Remember the apexes, as usual. 91 On TAB get SURNAME in records whose CITY is either 'rome' or 'paris' (do not use OR) SELECT surname FROM tab WHERE city IN('rome', 'paris');Usage of IN: whereas BETWEEN select a RANGE, IN lists a STRICT set of chances 92 On TAB get all the SURNAMEs that start with 'Es' SELECT surname FROM tab WHERE surname LIKE 'Es%';In a string the % char is the wildchar for any type of furhter charachters! NOTE: do NOT make the error to write things like fieldName='some%': wrong! The use of % (and of _ as you are to see next example in the line) REQUIRES the use of keyword LIKE. 93 On TAB get all the SURNAMEs that start with 'Es' followed by three more digits SELECT surname FROM tab WHERE surname LIKE 'Es___';In a string the _ char is the wildchar for one single charachter whatever it is: so three _ in a line means three chars and no more, whereas % would mean whatever char whatever AMOUNT too. See previous note too. 98 On TAB get SURNAME and NAME from records whose TITLE is 'professor' and whose HIRED is after year 2000 SELECT surname, name FROM tab WHERE title='professor' AND hired >='2001-01-01';Be precise with the date. 99 On TAB get PAYROLL for record whose SURNAME='Smith' and whose NAME='John', or also whose SURNAME='Brown' and NAME='Mary' Get the habit always to use parenthesis to conceptually isolate GROUPINGS of conditions 100 On TAB show all the DATES whose is above four days knowing that you have two fields for that STARTDATE and ENDDATE.
(the read tip) SELECT dates FROM tab WHERE (enddate-startdate)=4Normally calculations need keyword AS plus aFieldName to assign the newly generated result to a field: BUT IN THIS CASE since the result of this calculations is NOT TO BE SHOWN in the RESULTS but SIMPLY SERVES TO GRAB (filter) the result which is OTHER than it (in fact it is the field named dates) you do NOT use keyword AS after the calculation. 101 On TAb get the higher PRICE (then read note) SELECT MAX(price) FROM tab;This expression would work also without including an AS fieldName statement, although it is good habit to use such a statement when performing calculations. From now onward you'd use AS too. 104 On TAB which is the latest ORDERDATE (is a field name too) we have placed an ORDER (you want to list only one time such date) SELECT MAX(DISTINCT orderdate) FROM tab;It is assumed you want to get only ONE element and not all the possible records with the last date so you insert DISTINCT. 105 On TAB which SALARYDATE (is a field name too!) have you paied the first salary? (you want one report only) SELECT MIN(DISTINCT salarydate) FROM tabFirst, namely lower: MIN() 107 On TAB find the medium SALARY amount you pay. SELECT AVG(salary) FROM tab;AVG would need AS fieldName but apparently works without too. Salary fields can greatly vary we assume so you have to count them all (to undertsand why I say this check next question in the line). 108 On TAB find the medium SIZE of some item x (slightly tricky: try to optimize since you may have many items with the same size...) SELECT AVG(DISTINCT size) FROM tab;Use of DISTINCT avoids SQL calculating arguably hundreds of records: it just can do the same with one single instance for each given size, thence DISTINCT. 109 On TAB find how many records are there SELECT COUNT(*) FROM tabIn mySQL use of AS fieldName is not strictly necessary with COUNT. COUNT is used to count the RECORDS and NOT the values inside them: do not be confused with SUM. COUNT counts records amount regardless of the values. The only time COUNT can meddle with values is to SKIP identical ones (see next example) to avoid counting records that carry a value just met in case you need such feature. 112 On TAB find the whole gain reaped on DATEORDER
may 2002
knowing that prices are in field PRICE
(do not use >=) SELECT SUM(price) FROM tab WHERE dateorder BETWEEN '2002-05-01' AND '2002-05-31'Do not confuse SUM with COUNT: the former counts (sums) the values, the latter only counts how many records are there. 113 On TAB find all the AIRCOMPANY and for each of them sum all their FEES showing the results in a table where each AIRCOMPANY and its relative sum of prices is shown only one SELECT aircompany, SUM(price) FROM tab GROUP BY company;You're actually required to select 2 fields: aircompany and price: do not forget the comma!
You use GROUP BY. Do not confuse it with DISTINCT and with ORDER BY. ORDER BY is simply a sorting process in alphabetical order (you could actually ADD such sorting -ORDER BY- to a GROUPing BY if you want to GROUP BY a field1 BUT you want then to ORDER BY a field 2!), GROUP BY is still ALSO a sorting process but it also adds a further rationalization of the output: it AGGREGATES the output by removing DUPLICATES of the field indicated as the one you want to GROPY BY.
It differs from DISTINCT insofar by DISTINCT you skip the identical values DURING the process (that is: you won't SUM the identical values after it has recurred once) and GROPU BY, conversely, is processed AFTER ALL the previous operations have been performed (therefore the SUMs are done on all fields and don't skip, in our case, IDENTICAL prices) and simply imposes a 'distinct' AFTER that: REGROUPS AFTER THE PROCESS HAS FULLY FINISHED. It is a filter in EXIT, on OUTPUT whereas DISTINCT is a filter IN THE PROCESS.
Note that GROUP BY is MANDATORY with fields that AGGREGATE data such as SUM or MIN or MAX: in fact whereas the firstly selected field (in our case aircompany) may generate multiple entires (you have many aircompanies), the sums would necessarily generate a minor amount of entries in regard of the selected aircompanies, for it sums the fields of the prices thus generating an ASYMMETRY between the amount of aircompany fields SELECTed and the amount of SUMmed fields GENERATED: in fact while you sum their prices you're thus shrinking the amount of PRICEs fields available for aircompanies in the OUTPUT: consequently GROUPING BY is then indespensable a clause with SUM and the alike, in order to restore the symmetry between the columns: in fact GROUPing BY the aircompany names would report for each company only one record, thus recovering the simmetry (the SUM generates only one field for each aircompany in fact, for it eventually ends up with SUMs all the prices for EACH of them). Now, SQL can NOT return an ASYMMETRIC table, where one column is say 100 rows and another only, say, 20; if the latter (SUM) aggregates the prices for each aircompany showing the same name and it happens aircompanies are 5 on the whole (100/20 columns of SUMS means 5 brands of aircompanies scattered along the 100 records table, each arguably proposing a flight with a different price accordingly to the destination - an obvious implication not imediately transparent if you stick just to the query): you'd find yourself with 100 aircompany fields SELECTed as requested but only 20 prices fields derived. 114 On TAB find all the AIRCOMPANY and for each of them sum all their FEES showing the results in a table where each AIRCOMPANY and its relative sum of prices is shown only once per company, and list the results by the price with the higher prices on top (TRICKY) SELECT aircompany, SUM(price) AS someName FROM tab GROUP BY company ORDER BY someName DESC;See previous note too. Here you first group by and this produces an order by the exceeding records you want to level to the amount of sums yielded, but then you want to order by the sums so after the function has aggregated by GROUP BY to restore the symmetry, you can overcome the DEFAULT listing in aircompany alphabetical order that goes with GROUP BY and swap it with the order you may prefer after price. Note that ORDER BY is LOGICALLY a next step, therefore it can NOT be listed BEFORE the GROUP BY statement, ok? Note that since the fields generated by SUM are entirely new ones (not present in the original table) you do have now to declare a NEW NAME to this newly generated row of fields and then you have to use this latest and newly included name to sort by it. It is just an alias of price, but of the SUMs of such prices! As such you have to introduce it: if you'd say order by price, that won't order the way you expect for the query generated SUM fields that as such aggregate the price fields and are cinsequently LESS! Also, note you order by DESC if you want the lower at bottom. 115 On TAB find all the AIRCOMPANY and for each of them sum all their FEES showing the results in a table where each AIRCOMPANY and its relative sum of prices is shown only once per company, and list the results by the price with the higher prices on top but excluding all the prices lower than 2000 (arguably dollars. Include no dots in the figure)
You may want to read the tip too. SELECT aircompany, SUM(price) AS someName FROM tab GROUP BY company HAVING someName>2000 ORDER BY someName DESC;You use the keyword HAVING: such keyword can go ONLY with GROUP BY. In fact, it is a selection analogous to WHERE (that is: means sort of: grab out of the seleted fields and the generated sums only fields where/having value>2000 in our example) but has this difference: keyword WHERE performs a conditional comparison DURING the process and sifts out the records while SQL parses the table (that is, a clause WHERE in our example would have stripped off and discarded the incoming PRICE records being parsed BEFORE the eventual sum has been yielded, in case the price would have been noticed as >2000 upon first access! Thus all prices higher than 2000, with WHERE, would have not been taken in and would have NOT been... summed!), whereas HAVING performs the comparison only AFTER (AFTER!) all (ALL) the records have been gathered. Consider attentively: the query says this: select the fields and while you select them perform a sum; AFTER (AFTER!) you have the selected fields of the aircompanies and you have calculated the generated SUMs ENTIRELY, restore the generated output table SYMMETRY by GROUP BY; then on THESE data take in only those whose yielded EVENTUAL sum is <2000, and report only those; eventually, list alphabetically. The order of the statements strictly follows this convincing verbal logic.