linux
Table of Content

Weight: 2

Description: Candidates should be able to query databases and manipulate data using basic SQL commands. This objective includes performing queries involving joining of 2 tables and/or subselects.

Key Knowledge Areas:

  • Use of basic SQL commands
  • Perform basic data manipulation

Terms and Utilities:

insert
update
select
delete
from
where
group by
order by
join

mysql

mysql> SHOW DATABASES;
mysql> CREATE DATABASE test;
mysql> SHOW TABLES;
mysql> CREATE TABLE objects (name VARCHAR(30), color VARCHAR(20),
-> size FLOAT, hardness ENUM('soft', 'medium', 'hard'), 
-> value DECIMAL(10,2));

mysql> INSERT INTO objects
-> VALUES('lizard', 'green',6, 'soft', 10.00);

mysql> UPDATE objects SET size=5 WHERE name='lizard'; 

SELECT field(s) FROM table [ WHERE conditions ] [ORDER BY field]

mysql> SELECT value,color FROM objects;

SELECT * FROM objects WHERE color='green'
SELECT * FROM objects WHERE size>10;
SELECT * FROM objects WHERE name>'b' 
SELECT * FROM objects WHERE hardness='soft' AND value>7.50;

mysql> SELECT * FROM objects WHERE hardness='soft' ORDER BY value;

Combining Data from Multiple Tables

mysql> CREATE TABLE locations (id INTEGER, name VARCHAR(30),
-> location VARCHAR(30), cond INTEGER);
mysql> INSERT INTO locations VALUES(1, 'banana', 'kitchen', 9);

mysql> SELECT objects.name, objects.color, locations.location
-> FROM objects
-> JOIN locations
-> WHERE objects.name=locations.name AND objects.color='green'

mysql> SELECT objects.name, objects.value, SUM(value)
-> FROM objects, locations
-> WHERE locations.name=objects.name
-> GROUP BY value;

delete data

mysql> DELETE FROM locations
-> WHERE name='tree' AND location='back yard'; 

DELETE * from locations;
DROP TABLE locations;