SQL , Sql Server - rextester

SQL

--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;')

select * from metric_stats
where month=1
and temp_c<0
order by rain_c;

SELECT * FROM METRIC_STATS;


run \| edit \| history \| help	0