SQL for beginners( defined with errors while enforcing constraints), Sql Server

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