SQL Flashcards
How to create schema with utf8mb4 ?
CREATE SCHEMA new_schema DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
What statement do you run in your database command line to see the schema that you created?
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
What statement is used to create a table called “new_table” for a “new_schema”?
CREATE TABLE new_schema.new_table (
id INT NOT NULL COMMENT ‘This is a primary index’,
PRIMARY KEY (id)
);
What statement do I enter for showing full columns of the “new_table” that belongs to “new_schema”?
SHOW FULL COLUMNS FROM new_schema.new_table;
What statement do I enter to drop the “new_table” that belongs to “new_schema”?
DROP TABLE new_schema.new_table;
What types of integers can you store usually, and what’s the popular type ?
BIGINT, INT, MEDIUMINT, SMALLINT, TINYINT: Represent integers. The farther to the left, the larger the number that can be stored. The most common setting is INT, which can store values ranging from -2147483648 to 2147483647.
What kind of non-integers types can you input into a database usually?
DOUBLE, FLOAT, DECIMAL: Represent decimals, DECIMAL can be set to DECIMAL(5, 2), which means that the value of the column must be 5 digits, and two of the digits occur after the decimal point, such as a value like 666.88.
Which non-integer types are not precise and which non-integer type we should use for precision?
It should be noted that the values stored in DOUBLE and FLOAT are not precise. When you store 2.5 for related operations, it is likely to be calculated as 2.500000002. Therefore, DECIMAL is recommended for high-accuracy data system requirements.
How do you set to date, month, and year?
DATE, MONTH, YEAR
What datetime types are more precise than DATE?
DATETIME can accept a purely datetime value format like 8888-01-01 00:00:00, but TIMESTAMP is limited to between 1970-01-01 00:00:01 and 2038-01-19 03:14:07
What are the TEXT types and what are they suitable for? What’s the popularly used type?
CHAR, VARCHAR: Store plain text, the former is suitable for data with fixed text length, such as currency abbreviations. The most common default setting is VARCHAR(45) for MySQL, which means each item in the column can store 45 characters.
TEXT, LONGTEXT: If you want to store text data whose maximum length is unknown, you can use TEXT related data type settings.
What are the special data types for file?
There are also several special data types: 1. BINARY, BLOB: Store data of file type, such as images or videos. BLOB (Binary Large Object) is not only for those types but also for large unstructured data. However, it is rarely used in practice because it is difficult to manage files through database software.
What are the 2nd or third special data types?
- BOOLEAN: Store the data of the logical operand. In simple terms, it is the data true or false. The database will replace it with 1 or 0 respectively. Suppose there is a column called is_alive, then each value is either true or false. 3. JSON: A very common data format in modern data exchange. We will provide more details on this data type in the SQL Syntax: JSON chapter.
What is the purpose of column attribute function?
The purpose of the column attribute function is to ensure that when new data is added to the table, the database will process the data format or content in advance.
What does NOT NULL mean?
- NOT NULL: It means that the value in the field cannot be NULL. So when we use NOT NULL, we are forcing the column to NOT accept null values, which means the field must always contain a value. In the world of programming languages, NULL means nothing. For example, if the value is blank text, it means at least blank text, but it cannot be anything. In the real world, it can be imagined that it is like the contrast between air and vacuum. Although air cannot be seen, it is still there, but the vacuum is really nothing. For example, if the value is blank text, it means at least blank text, but it cannot be anything. In the real world, it can be imagined that it is like the contrast between air and vacuum. Although air cannot be seen, it is still there, but the vacuum is really nothing.
What are the other column attributes other than NOT NULL?
- AUTO_INCREMENT: In the database, it will automatically generate the column’s values one by one using numbers. It is similar to a serial number. The database software will help us avoid the problem of repeated serial numbers, so you don’t need to write your own programs to compare and set the value. 3. DEFAULT: You can set the default value of the column because the data to be inserted in the field may be empty in some circumstances.
How to enter a new table “new_table” that belong to “new_schema” for two columns - id, name - and it has a default value of “N/A”?
CREATE SCHEMA new_schema DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE new_schema.users (
id INT NOT NULL AUTO_INCREMENT COMMENT ‘This is the primary index’,
name VARCHAR(45) NOT NULL DEFAULT ‘N/A’,
PRIMARY KEY (id)
);
How does AUTO_INCREMENT work in Oracle and PostgresSQL?
In PostgreSQL, the function of AUTO_INCREMENT will be replaced by the SERIAL setting, and Oracle will use IDENTITY. Therefore, by focusing on what each function can do rather than “what each function is,” you can become more adaptable to different database systems.
How to add a column of “age” after “name” to an already-existing table of “new_table” that belongs to “new_schema”?
ALTER TABLE new_schema.users
ADD COLUMN age INT NULL AFTER name;
How to change an existing table with existing id, name, username columns to have id with id have INT(11) and to rename ‘name’ to ‘username’ to have ‘No Name’ as default values?
ALTER TABLE new_schema.users
CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT,
CHANGE COLUMN name user_name VARCHAR(45) NOT NULL DEFAULT ‘No Name’;
