Database Design Exam 1 – Complete Study Guide, Key Concepts & Practice
Question.
what is SQL -communication with databases
-provide a simple and efficient way to read and write data from a database
what do we do with sql -DDL
-DML
-DQL
-DCL
DDL (Data definition Language) - Creating a database and defining its structure
- CREATE, ALTER, and DROP statements
DML (Data Manipulation Language) INSERT, UPDATE, and DELETE statements
DQL (Data Query Language) - Querying the database to obtain the data necessary
to answer questions
- Pose a query (basically a question) to the database
- The database then provides the data that answer your query
- SELECT statement
Data Control Language - Manage privileges that database users have regarding
the database object
- GRANT and REVOKE statements
• Controlling data base security
•Database administrators handle database security
Why do we define Data Types? -Data types enables you to restrict the type of data that can be stored in a
column
-Data types allow for more efficient storage internally
-Data types allow for alternate sorting orders
CHAR(n) a Fixed-length string of n characters
•will ALWAYS be of a set length, such as state abbreviation
VARCHAR(n) string of up to n characters
• Similar to CHAR except the length of the text is variable
• Only uses up memory for the actual number of characters stored
• Flexible and can adapt to the length of your data
• The size is a maximum, not a minimum
TEXT Large string, maximum length is up to 65,535 characters; don't need to specify
the maximum number of characters
TINYTEXT Holds a string with a max length of 255 characters
MEDIUMTEXT Holds a string with a max length of 16,777,215 characters
1/7
, LONGTEXT Holds a string with a max length of 4,294,967,295 characters
ENUM accepts one of a predefined set of up to 64K strings
e.g., Continent: 'Asia', 'Europe', 'North America'
When we create the table with the column Continent, we specify its data type
as ENUM as follows:
ENUM ('Asia', 'Europe', 'North America')
INT - Whole numbers only rather than numbers with decimal points
- You can also specify the maximum number of digits: INT(size)
DEC(length, Decimal) The length is the total number of digits that can be stored and decimal is the
number of digits after the decimal
DATE YYY-MM-DD
TIME HH:MM:SS
DATETIME In the format of YYY-MM-DD HH:MM:SS
TIMESTAMP In the format of YYYMMDDHHMMSS
BLOB Binary Datatypes are used to store all sorts of data, such as graphic images,
multimedia, and word processor documents.
-max length of 64k
MEDIUMBLOB BLOB with max length of 16MB
LONGBLOB BLOB with max length of 4GB
Quatations - String values (e.g., CHAR, VARCHAR, BLOB)
must always be surrounded by single quotes'
- Date and Time values must always be quoted
- Numeric values shouldn't be quoted
- Functions cannot be quoted
what if I have a single quote inside a string value? *Handle quotes with a backslash:
Put a backslash in front of the single quote to indicate that ' inside a string quote
to tell SQL that a single quote is part of a text string
What do tables look like? DESC table_name; = SHOW columns FROM table_name;
SHOW TABLES;
2/7
Question.
what is SQL -communication with databases
-provide a simple and efficient way to read and write data from a database
what do we do with sql -DDL
-DML
-DQL
-DCL
DDL (Data definition Language) - Creating a database and defining its structure
- CREATE, ALTER, and DROP statements
DML (Data Manipulation Language) INSERT, UPDATE, and DELETE statements
DQL (Data Query Language) - Querying the database to obtain the data necessary
to answer questions
- Pose a query (basically a question) to the database
- The database then provides the data that answer your query
- SELECT statement
Data Control Language - Manage privileges that database users have regarding
the database object
- GRANT and REVOKE statements
• Controlling data base security
•Database administrators handle database security
Why do we define Data Types? -Data types enables you to restrict the type of data that can be stored in a
column
-Data types allow for more efficient storage internally
-Data types allow for alternate sorting orders
CHAR(n) a Fixed-length string of n characters
•will ALWAYS be of a set length, such as state abbreviation
VARCHAR(n) string of up to n characters
• Similar to CHAR except the length of the text is variable
• Only uses up memory for the actual number of characters stored
• Flexible and can adapt to the length of your data
• The size is a maximum, not a minimum
TEXT Large string, maximum length is up to 65,535 characters; don't need to specify
the maximum number of characters
TINYTEXT Holds a string with a max length of 255 characters
MEDIUMTEXT Holds a string with a max length of 16,777,215 characters
1/7
, LONGTEXT Holds a string with a max length of 4,294,967,295 characters
ENUM accepts one of a predefined set of up to 64K strings
e.g., Continent: 'Asia', 'Europe', 'North America'
When we create the table with the column Continent, we specify its data type
as ENUM as follows:
ENUM ('Asia', 'Europe', 'North America')
INT - Whole numbers only rather than numbers with decimal points
- You can also specify the maximum number of digits: INT(size)
DEC(length, Decimal) The length is the total number of digits that can be stored and decimal is the
number of digits after the decimal
DATE YYY-MM-DD
TIME HH:MM:SS
DATETIME In the format of YYY-MM-DD HH:MM:SS
TIMESTAMP In the format of YYYMMDDHHMMSS
BLOB Binary Datatypes are used to store all sorts of data, such as graphic images,
multimedia, and word processor documents.
-max length of 64k
MEDIUMBLOB BLOB with max length of 16MB
LONGBLOB BLOB with max length of 4GB
Quatations - String values (e.g., CHAR, VARCHAR, BLOB)
must always be surrounded by single quotes'
- Date and Time values must always be quoted
- Numeric values shouldn't be quoted
- Functions cannot be quoted
what if I have a single quote inside a string value? *Handle quotes with a backslash:
Put a backslash in front of the single quote to indicate that ' inside a string quote
to tell SQL that a single quote is part of a text string
What do tables look like? DESC table_name; = SHOW columns FROM table_name;
SHOW TABLES;
2/7
