sample sql database file to explain SQL

exercises.md

Exercises

In order to learn SQL, we'll go through some simple exercises to start

1. Given the following statement:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
);

• What does the AUTO_INCREMENT flag do?
• What does int(11) mean?
• What is users in this statement?

2. Explain the concept of a primary key.
3. Explain the concept of a foreign key.

Queries

All the following questions deal with the attached SQL database. Make sure you follow the directions in the Google Doc on how to import the database.

1. Get a list of all the usernames from the users table.
2. Get the name of all users who have placed orders for an iPhone
3. Get a list of all the users (id's are fine) who have placed orders over 300$. N.B. The cost in the products table is in cents, not dollars.

sample_db.sql

#
# Dump of table orders
# ------------------------------------------------------------

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `placed` datetime DEFAULT NULL,
  `filled` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `orders` WRITE;
/*!40000 ALTER TABLE `orders` DISABLE KEYS */;

INSERT INTO `orders` (`id`, `user_id`, `placed`, `filled`)
VALUES
  (1,1,'2013-05-22 16:13:35',0);

/*!40000 ALTER TABLE `orders` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table orders_products
# ------------------------------------------------------------

DROP TABLE IF EXISTS `orders_products`;

CREATE TABLE `orders_products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(11) unsigned NOT NULL,
  `product_id` int(11) unsigned NOT NULL,
  `quantity` int(2) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `orders_products` WRITE;
/*!40000 ALTER TABLE `orders_products` DISABLE KEYS */;

INSERT INTO `orders_products` (`id`, `order_id`, `product_id`, `quantity`)
VALUES
	(1,1,1,1),
	(2,1,2,1);

/*!40000 ALTER TABLE `orders_products` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table products
# ------------------------------------------------------------

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `descirption` text,
  `cost` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `products` WRITE;
/*!40000 ALTER TABLE `products` DISABLE KEYS */;

INSERT INTO `products` (`id`, `name`, `descirption`, `cost`)
VALUES
	(1,'iPhone','An iPhone!',49999),
	(2,'Nexus 4','A Nexus 4!',30999),
	(3,'iPad','An iPad!',39999);

/*!40000 ALTER TABLE `products` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table shopping_carts
# ------------------------------------------------------------

DROP TABLE IF EXISTS `shopping_carts`;

CREATE TABLE `shopping_carts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `product_id` int(11) unsigned NOT NULL,
  `quantity` int(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `shopping_carts` WRITE;
/*!40000 ALTER TABLE `shopping_carts` DISABLE KEYS */;

INSERT INTO `shopping_carts` (`id`, `user_id`, `product_id`, `quantity`)
VALUES
	(1,1,1,1),
	(2,1,2,2),
	(3,1,3,1),
	(4,3,1,1);

/*!40000 ALTER TABLE `shopping_carts` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table users
# ------------------------------------------------------------

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL DEFAULT '',
  `password` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;

INSERT INTO `users` (`id`, `email`, `password`)
VALUES
	(1,'dave@bitmakerlabs.com','962012d09b8170d912f0669f6d7d9d07'),
	(2,'will@bitmakerlabs.com','912ec803b2ce49e4a541068d495ab570'),
	(3,'rob@gmail.com','fd2cc6c54239c40495a0d3a93b6380eb');

/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;