03 - MySQL

Question 1

What is SQL?

Answer 1

Structured Query Language. It is a comprehensive database language. Standard for relational databases.

Question 2

What is MySQL?

Answer 2

RDBMS. Created by MySQL AB and bought out by Sun Microsystems (Oracle). MySQL supports: Web applications, Open-Source, low overhead, large size table.

Question 3

SQL: Login to mysql?

Answer 3

$ mysql -u root -p

Question 4

SQL: List all current databases?

Answer 4

$ SHOW DATABASES;

Question 5

SQL: Create database?

Answer 5

$ CREATE DATABASE [db_name];

Question 6

SQL: Delete database?

Answer 6

$ DROP DATABASE [db_name];

Question 7

SQL: Make database active?

Answer 7

$ USE [db_name];

Question 8

SQL: Show tables in the current database?

Answer 8

$ SHOW TABLES;

Question 9

SQL: Create table with three fields called Officers?

Answer 9

$ CREATE TABLE Officers (BadgeNo int, Name varchar(40), Rank varchar(10));

Question 10

SQL: Insert entry into table?

Answer 10

$ INSERT INTO Officers VALUES (132, “Ann G”, “Dep”);

Question 11

SQL: How to remove a table?

Answer 11

$ DROP TABLE [tbl_name];

Question 12

Name Numeric Data Types?

Answer 12

• int - is used for integer (whole) numbers
• float - is used for floating point (decimal) numbers

Question 13

Name Time Related Data Types?

Answer 13

• date
• time
• datetime

Date format: “yyyy-mm-dd”

Time format: “hh:mm:ss”

Question 14

Name String Data Types?

Answer 14

• Char - fixed size (3 Bytes)
• Varchar - variable size
• text

Char and varchar are good up to 255 characters. If you wish to store more text than this you should use the text data type. This takes no arguments and gives a much larger storage capacity.

Question 15

SQL: How to insert values from a CSV file?

Answer 15

LOAD DATA LOCAL INFILE ‘C:/Officers.csv’ INTO TABLE Officers FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”’;