. BACK TO HOME - IJHSS - ISC - E-Accounts SQL COMMANDS

1. Select all the codes inside box below left ( #MySQL 5.7.12.....till.....as 'mysql version' ) and delete it
2. copy + paste the codes Examples:, given below right (CREATE TABLE IF NOT...)or type your sql commands after creating a table.
3. click >> Run it (F8)
Examples 1:
CREATE TABLE IF NOT EXISTS ISC (
admitNo INT UNSIGNED NOT NULL AUTO_INCREMENT,
rollNo INT NOT NULL DEFAULT '0',
name VARCHAR(30) NOT NULL DEFAULT ' ',
total INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (admitNo)
);
DESCRIBE ISC;
INSERT INTO isc VALUES (1201, 1, 'Anand', 400);
INSERT INTO isc (rollNo, name, total) VALUES
(4, 'Ramesh', 440),
(5, 'Joseph', 430);
SELECT * FROM isc;

Example 2 - contains:
creating a table
creating a view
inserting rows
updating rows
deleting rows
commit -- making changes permanent
rollback -- undoing temporary changes
enforcing integrity constraints
using an Embedded C program
INTERACTIVE SQL EXAMPLES
create a table to store information about weather observation stations:
#NAME?
CREATE TABLE STATION1
(ID INTEGER PRIMARY KEY,
CITY CHAR(20),
STATE CHAR(2),
LAT_N REAL,
LONG_W REAL);

populate the table STATION1 with a few rows:

INSERT INTO STATION1 VALUES (13, 'Phoenix', 'AZ', 33, 112);
INSERT INTO STATION1 VALUES (44, 'Denver', 'CO', 40, 105);
INSERT INTO STATION1 VALUES (66, 'Caribou', 'ME', 47, 68);

query to look at table STATION1 in undefined order:

SELECT * FROM STATION1;
ID CITY STATE LAT_N LONG_W
13 Phoenix AZ 33 112
44 Denver CO 40 105
66 Caribou ME 47 68
query to select Northern STATION1s (Northern latitude > 39.7):
-- selecting only certain rows is called a "restriction".

SELECT * FROM STATION1
WHERE LAT_N > 39.7;
ID CITY STATE LAT_N LONG_W
44 Denver CO 40 105
66 Caribou ME 47 68
query to select only ID, CITY, and STATE columns:
-- selecting only certain columns is called a "projection".

SELECT ID, CITY, STATE FROM STATION1;
ID CITY STATE
13 Phoenix AZ
44 Denver CO
66 Caribou ME
query to both "restrict" and "project":

SELECT ID, CITY, STATE FROM STATION1
WHERE LAT_N > 39.7;
ID CITY STATE
44 Denver CO
66 Caribou ME
create another table to store normalized temperature and precipitation data:
#NAME?
(so name and location will be known).
#NAME?
#NAME?
#NAME?
#NAME?

CREATE TABLE STATS
(ID INTEGER REFERENCES STATION1(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));

populate the table STATS with some statistics for January and July:

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

query to look at table STATS in undefined order:

SELECT * FROM STATS;
ID MONTH TEMP_F RAIN_I
13 1 57.4 0.31
13 7 91.7 5.15
44 1 27.3 0.18
44 7 74.8 2.11
66 1 6.7 2.1
66 7 65.8 4.52
query to look at table STATS, picking up location information by joining with table STATION1 on the ID column:
-- matching two tables on a common column is called a "join".
#NAME?
#NAME?

SELECT * FROM STATION1, STATS
WHERE STATION1.ID = STATS.ID;
ID CITY ST LAT_N LONG_W ID MONTH TEMP_F RAIN_I
13 Phoenix AZ 33 112 13 1 57.4 0.31
13 Phoenix AZ 33 112 13 7 91.7 5.15
44 Denver CO 40 105 44 1 27.3 0.18
44 Denver CO 40 105 44 7 74.8 2.11
66 Caribou ME 47 68 66 1 6.7 2.1
66 Caribou ME 47 68 66 7 65.8 4.52


query to look at the table STATS, ordered by month and greatest rainfall, with columns rearranged:

SELECT MONTH, ID, RAIN_I, TEMP_F
FROM STATS
ORDER BY MONTH, RAIN_I DESC;
MONTH ID RAIN_I TEMP_F
1 66 2.1 6.7
1 13 0.31 57.4
1 44 0.18 27.3
7 13 5.15 91.7
7 66 4.52 65.8
7 44 2.11 74.8


query to look at temperatures for July from table STATS, lowest temperatures first, picking up city name and latitude by joining with table STATION1 on the ID column:

SELECT LAT_N, CITY, TEMP_F
FROM STATS, STATION1
WHERE MONTH = 7
AND STATS.ID = STATION1.ID
ORDER BY TEMP_F;
LAT_N CITY TEMP_F
47 Caribou 65.8
40 Denver 74.8
33 Phoenix 91.7


query to show MAX and MIN temperatures as well as average rainfall for each STATION1:

SELECT MAX(TEMP_F), MIN(TEMP_F), AVG(RAIN_I), ID
FROM STATS
GROUP BY ID;
MAX(TEMP_F) MIN(TEMP_F) AVG(RAIN_I) ID
91.7 57.4 2.73 13
74.8 27.3 1.145 44
65.8 6.7 3.31 66


query (with subquery) to show STATION1s with year-round average temperature above 50 degrees:
#NAME?

SELECT * FROM STATION1
WHERE 50 < (SELECT AVG(TEMP_F) FROM STATS
WHERE STATION1.ID = STATS.ID);
ID CITY ST LAT_N LONG_W
13 Phoenix AZ 33 112
44 Denver CO 40 105


create a view (derived table or persistent query) to convert Fahrenheit to Celsius and inches to centimeters:

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;

query to look at table STATS in a metric light (through the new view):

SELECT * FROM METRIC_STATS;
ID MONTH TEMP_C RAIN_C
13 1 14.1111111 0.122047
13 7 33.1666667 2.027555
44 1 -2.6111111 0.070866
44 7 23.7777778 0.830707
66 1 -14.055556 0.82677
66 7 18.7777778 1.779524


another metric query restricted to January below-freezing (0 Celsius) data, sorted on rainfall:

SELECT * FROM METRIC_STATS
WHERE TEMP_C < 0 AND MONTH = 1
ORDER BY RAIN_C;
ID MONTH TEMP_C RAIN_C
44 1 -2.6111111 0.070866
66 1 -14.055556 0.82677


Interactive 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.01;

and take a look:

SELECT * FROM STATS;
ID MONTH TEMP_F RAIN_I
13 1 57.4 0.32
13 7 91.7 5.16
44 1 27.3 0.19
44 7 74.8 2.12
66 1 6.7 2.11
66 7 65.8 4.53


update one row, Denver's July temperature reading, to correct a data entry error:

UPDATE STATS SET TEMP_F = 74.9
WHERE ID = 44
AND MONTH = 7;

and take a look:

SELECT * FROM STATS;
ID MONTH TEMP_F RAIN_I
13 1 57.4 0.32
13 7 91.7 5.16
44 1 27.3 0.19
44 7 74.9 2.12
66 1 6.7 2.11
66 7 65.8 4.53


make the above changes permanent:
#NAME?

COMMIT WORK;

update two rows, Denver's rainfall readings:

UPDATE STATS SET RAIN_I = 4.50
WHERE ID = 44;

and take a look:

SELECT * FROM STATS;
ID MONTH TEMP_F RAIN_I
13 1 57.4 0.32
13 7 91.7 5.16
44 1 27.3 4.5
44 7 74.9 4.5
66 1 6.7 2.11
66 7 65.8 4.53


Oops! We meant to update just the July reading! Undo that update:
#NAME?

ROLLBACK WORK;

and take a look:

SELECT * FROM STATS;
ID MONTH TEMP_F RAIN_I
13 1 57.4 0.32
13 7 91.7 5.16
44 1 27.3 0.19
44 7 74.9 2.12
66 1 6.7 2.11
66 7 65.8 4.53


now update Denver's July rainfall reading and make it permanent:

UPDATE STATS SET RAIN_I = 4.50
WHERE ID = 44
AND MONTH = 7;

COMMIT WORK;

and take a look:

SELECT * FROM STATS;
ID MONTH TEMP_F RAIN_I
13 1 57.4 0.32
13 7 91.7 5.16
44 1 27.3 0.19
44 7 74.9 4.5
66 1 6.7 2.11
66 7 65.8 4.53


delete July data and East Coast data from both tables:
-- note that we use longitude values from the related STATION1 table to determine which STAT STATION1s were east of 90 degrees.

DELETE FROM STATS
WHERE MONTH = 7
OR ID IN (SELECT ID FROM STATION1
WHERE LONG_W < 90);

DELETE FROM STATION1 WHERE LONG_W < 90;

COMMIT WORK;

and take a look:

SELECT * FROM STATION1;
ID CITY ST LAT_N LONG_W
13 Phoenix AZ 33 112
44 Denver CO 40 105
SELECT * FROM STATS;
ID MONTH TEMP_F RAIN_I
13 1 57.4 0.32
44 1 27.3 0.19


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;
ID MONTH TEMP_C RAIN_C
13 1 14.1111111 0.125984
44 1 -2.6111111 0.074803
SQL Constraints
SQL enforces data integrity constraints.
Attempt to insert a row for an unknown observation STATION1.
-- The ID value of 33 does not match a STATION1 ID value in the STATION1 table.
#NAME?

INSERT INTO STATS VALUES (33,8,27.4,.19);
error message
violation of constraint STATS_FOREIGN1 caused operation to fail


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;
error message
violation of constraint STATS_CHECK2 caused operation to fail


Attempt to insert a row with negative rainfall measurement, outside the range 0 to 100.

INSERT INTO STATS VALUES (44,8,27.4,-.03);
error message
violation of constraint STATS_CHECK3 caused operation to fail


Attempt to insert a row with month 13, outside the range of 1 to 12.

INSERT INTO STATS VALUES (44,13,27.4,.19);
error message
violation of constraint STATS_CHECK1 caused operation to fail


Attempt to insert a row with a temperature above the range -80 TO 150.

INSERT INTO STATS VALUES (44,8,160,.19);
error message
violation of constraint STATS_CHECK2 caused operation to fail


Attempt to insert a row with no constraint violations.

INSERT INTO STATS VALUES (44,8,27.4,.10);
status message
1 row inserted


and take a look:

SELECT * FROM STATS;
ID MONTH TEMP_F RAIN_I
44 8 27.4 0.1
13 1 57.4 0.32
44 1 27.3 0.19


Attempt to insert a second row of August statistics for STATION1 44.
#NAME?
#NAME?

INSERT INTO STATS VALUES (44,8,160,.19);
error message
violation of constraint STATS_PRIMARY_ID_MONTH caused operation to fail

Example 3:

1. The create table statement is used to create a new table. Here is the format of a simple create table statement:
create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type");
Example:
create table employee
(first varchar(15),
last varchar(20),
age number(3),
address varchar(30),
city varchar(20),
state varchar(20));
2. The insert statement is used to insert or add a row of data into the table.
insert into "tablename"
(first_column,...last_column)
values (first_value,...last_value);
Example:
insert into employee
(first, last, age, address, city, state)
values ('Luke', 'Duke', 45, '2130 Boars Nest',
'Hazard Co', 'Georgia');
3. The update statement is used to update or change records that match a specified criteria. This is accomplished by carefully constructing a where clause.
update "tablename"
set "columnname" =
"newvalue"
[,"nextcolumn" =
"newvalue2"...]
where "columnname"
OPERATOR "value"
[and|or "column"
OPERATOR "value"];
[] = optional
[The above example was line wrapped for better viewing on this Web page.]
Examples:
update phone_book
set area_code = 623
where prefix = 979;
update phone_book
set last_name = 'Smith', prefix=555, suffix=9292
where last_name = 'Jones';
update employee
set age = age+1
where first_name='Mary' and last_name='Williams';

BACK TO HOME