Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
SQL for beginners( defined with errors while enforcing constraints)
--Sql Server 2014 Express Edition --Batches are separated by 'go' select @@version as 'sql server version' CREATE TABLE STATION (ID INTEGER PRIMARY KEY, CITY CHAR(20), STATE CHAR(2), LAT_N REAL, LONG_W REAL); INSERT INTO STATION VALUES (13, 'Phoenix', 'AZ', 33, 112); INSERT INTO STATION VALUES (44, 'Denver', 'CO', 40, 105); INSERT INTO STATION VALUES (66, 'Caribou', 'ME', 47, 68); /*query to display full table*/ select * from station; /*query to select Northern stations (Northern latitude > 39.7): -- selecting only certain rows is called a "restriction".*/ SELECT * FROM STATION WHERE LAT_N > 39.7; /*query to select only ID, CITY, and STATE columns: -- selecting only certain columns is called a "projection".*/ SELECT ID, CITY, STATE FROM STATION; /* query to both restrict and projection*/ SELECT ID, CITY, STATE FROM STATION WHERE LAT_N > 39.7; CREATE TABLE STATS (ID INTEGER REFERENCES STATION(ID), MONTH INTEGER CHECK (MONTH BETWEEN 1 AND 12), TEMP_F REAL CHECK (TEMP_F BETWEEN -80 AND 150), RAIN_I REAL CHECK (RAIN_I BETWEEN 0 AND 100), PRIMARY KEY (ID, MONTH)); INSERT INTO STATS VALUES (13, 1, 57.4, 0.31); INSERT INTO STATS VALUES (13, 7, 91.7, 5.15); INSERT INTO STATS VALUES (44, 1, 27.3, 0.18); INSERT INTO STATS VALUES (44, 7, 74.8, 2.11); INSERT INTO STATS VALUES (66, 1, 6.7, 2.10); INSERT INTO STATS VALUES (66, 7, 65.8, 4.52); select * from STATS; /*query to look at table STATS, picking up location information by joining with table STATION on the ID column: -- matching two tables on a common column is called a "join". -- the column names often match, but this is not required. -- only the column values are required to match.*/ select * from station,stats where station.id=stats.id; /*query to look at the table STATS, ordered by month and greatest rainfall, with columns rearranged:*/ /*It by defaults take month ordered in ascending*/ select month,id,rain_i,temp_f from stats order by month,rain_i desc; /*query to look at the table STATS, ordered by month and greatest rainfall, with columns rearranged:*/ /*It is ordered in descending orader. */ select month,id,rain_i,temp_f from stats order by month desc,rain_i desc; /*query to look at temperatures for July from table STATS, lowest temperatures first, picking up city name and latitude by joining with table STATION on the ID column:*/ select lat_n,city, temp_f from station,stats where month=7 and station.id=stats.id order by temp_f; /*query to look at the table STATS, ordered by month and greatest rainfall, with columns rearranged:*/ SELECT MAX(TEMP_F), MIN(TEMP_F), AVG(RAIN_I), ID FROM STATS GROUP BY ID; /*query (with subquery) to show stations with year-round average temperature above 50 degrees:*/ -- rows are selected from the STATION table based on related values in the STATS table. SELECT * FROM STATION WHERE 50 < (SELECT AVG(TEMP_F) FROM STATS WHERE STATION.ID = STATS.ID); /*Creating a view for converting rainfall from inches to centimeters and temperature from fareignhiet to celcius*/ execute ('CREATE VIEW METRIC_STATS (ID, MONTH, TEMP_C, RAIN_C) AS SELECT ID, MONTH, (TEMP_F - 32) * 5 /9, RAIN_I * 0.3937 FROM STATS;') /*another metric query restricted to January below-freezing (0 Celsius) data, sorted on rainfall:*/ select * from metric_stats where month=1 and temp_c<0 order by rain_c; /*query to look at table STATS in a metric light (through the new view):*/ SELECT * FROM METRIC_STATS; /*SQL update examples*/ /*update all rows of table STATS to compensate for faulty rain gauges known to read 0.01 inches low*/ update stats set RAIN_I = RAIN_I +0.1; /*update one row, Denver's July temperature reading to 74.9, to correct a data entry error:*/ update stats set temp_F = 74.9 where month = 7 and id = 44; /*Look up the stats table*/ select * from stats; /*update denver's rainfall of july to 4.50 */ update stats set rain_i = 4.50 where month = 7 and id = 44; /*Look up the stats table*/ select * from stats; /*delete July data and East Coast data from both tables: -- note that we use longitude values from the related STATION table to determine which STAT stations were east of 90 degrees.*/ delete from stats where month =7 or id in(select id from station where long_w<90); delete from station where long_w<90; /*Look up station table*/ select * from station; /*Look up stats table*/ select * from stats; /*View METRIC_STATS, a Fahrenheit-to-Centigrade and inches-to-centimeters conversion of table STATS, reflects the updates made to the underlying table.*/ select * from metric_stats; /*SQL enforces data integrity constraints.*/ --Below are some error on violation of constraints /* to insert a row for an unknown observation station. -- The ID value of 33 does not match a station ID value in the STATION table. -- This is a violation of referential integrity.*/ INSERT INTO STATS VALUES (33,8,27.4,.19); /*Attempt to update a row with a temperature below the range -80 TO 150.*/ UPDATE STATS SET TEMP_F = -100 WHERE ID = 44 AND MONTH = 1; /*Attempt to insert a row with negative rainfall measurement, outside the range 0 to 100.*/ INSERT INTO STATS VALUES (44,8,27.4,-.03); /*Attempt to insert a row with month 13, outside the range of 1 to 12.*/ INSERT INTO STATS VALUES (44,13,27.4,.19); /*Attempt to insert a row with a temperature above the range -80 TO 150.*/ INSERT INTO STATS VALUES (44,8,160,.19); /*Attempt to insert a row with no constraint violations.*/ insert into stats values(44,8,27.4,.10); /*Look up stats table*/ select * from stats; --Attempt to insert a second row of August statistics for station 44. -- This is a violation of the primary key constraint. -- Only one row for each station and month combination is allowed. INSERT INTO STATS VALUES (44,8,160,.19);
run
|
edit
|
history
|
help
0
tableB
Libros
exp 8 XIV
String between two indexes
samplequery
stackoverflow_mysql_demo_data
db2
Comercio
newtable
mysql4