CREATE TABLE IF NOT EXISTS `ITEMS` (
`ID` int(6) unsigned NOT NULL,
`DESCRIPTION` varchar(200) NOT NULL,
`OWNER_ID` int(6) unsigned NOT NULL,
PRIMARY KEY (`ID`)
) DEFAULT CHARSET=utf8;
INSERT INTO `ITEMS` (`ID`, `DESCRIPTION`, `OWNER_ID`) VALUES
('1', 'Peanut Butter', '100'),
('2', 'Jelly', '200');
CREATE TABLE IF NOT EXISTS `OWNERS` (
`ID` int(6) unsigned NOT NULL,
`USER_ID` int(6) unsigned NOT NULL,
PRIMARY KEY (`ID`)
) DEFAULT CHARSET=utf8;
INSERT INTO `OWNERS` (`ID`, `USER_ID`) VALUES
('100', '1' ),
('200', '3' ),
('300', '2' ),
('400', '4' );
CREATE TABLE IF NOT EXISTS `USERS` (
`ID` int(6) unsigned NOT NULL,
`NAME_FIRST` varchar(200) NOT NULL,
`NAME_LAST` varchar(200) NOT NULL,
`USERNAME` varchar(200) NOT NULL,
PRIMARY KEY (`ID`)
) DEFAULT CHARSET=utf8;
INSERT INTO `USERS` (`ID`, `NAME_FIRST`, `NAME_LAST`, `USERNAME`) VALUES
('1', 'Wesley', 'Crusher', 'wcrusher'),
('2', 'Tommy', 'Wiseau', 'twiseau' ),
('3', 'Geddy', 'Lee', 'glee' ),
('4', 'Betty', 'White', 'bwhite' );
SELECT * FROM ITEMS; SELECT * FROM OWNERS; SELECT * FROM USERS;
/*
SQL Exercise:
Write a query to select the following for every
row in the ITEMS table:
- description
- the owner's first and last name in one column separated by a space
- the owner’s username
- Order the results by the item’s description in alphabetical order.
Once you've written your query, please use the 'Run SQL code' button
in the tool to execute it.
We'll be able to see your screen throughout the exercise.
When you're satisfied with your results, please let us know that
this is your final query and explain your thought process.
*/
Telegram: DB developers channel