Which of the following statement will add a column F_name to the student table?
In the previous chapter we have learned how to create a database on the database server. Now it's time to create some tables inside our database that will actually hold the data. A database table simply organizes the information into rows and columns. Show
The SQL SyntaxThe basic syntax for creating a table can be given with: CREATE TABLE table_name ( column1_name data_type constraints, column2_name data_type constraints, .... ); To understand this syntax easily, let's create a table in our demo database. Type the following statement on MySQL command-line tool and press enter:
The above statement creates a table named persons with four columns id, name, birth_date and phone. Notice that each column name is followed by a data type declaration; this declaration specifies that what type of data the column will store, whether integer, string, date, etc. Some data types can be declared with a length parameter that indicates how many characters can be stored in the column. For example, Note: The data type of the columns may vary depending on the database system. For example, MySQL and SQL Server supports The following table summarizes the most commonly used data types supported by MySQL. Data TypeDescription INT Stores numeric values in the range of -2147483648 to 2147483647DECIMAL Stores decimal values with exact precision. 0Stores fixed-length strings with a maximum size of 255 characters. 1Stores variable-length strings with a maximum size of 65,535 characters. 2Stores strings with a maximum size of 65,535 characters. 3Stores date values in the YYYY-MM-DD format. 4Stores combined date/time values in the YYYY-MM-DD HH:MM:SS format. 5Stores timestamp values. 5 values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:01' UTC).Please check out the reference section SQL DB data types for the detailed information on all the data types available in popular RDBMS like MySQL, SQL Server, etc. There are a few additional constraints (also called modifiers) that are set for the table columns in the preceding statement. Constraints define rules regarding the values allowed in columns.
We will learn more about the SQL constraints in next chapter. Note: The Microsoft SQL Server uses the Data Type2 property to perform an auto-increment feature. The default value is Data Type3 which means the seed or starting value is 1, and the incremental value is also 1. Tip: You can execute the command Data Type4 to see the column information or structure of any table in MySQL and Oracle database, whereas Data Type5 in SQL Server (replace the table_name with actual table name). Create Table If Not ExistsIf you try to create a table that is already exists inside the database you'll get an error message. To avoid this in MySQL you can use an optional clause Data Type6 as follow:
Tip: If you want to see the list of tables inside the currently selected database, you can execute Data Type7 statement on the MySQL command line. Which of the following statement would add a column to table already created?The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
Which of the following queries will you use to modify the students table to add a primary key on the student ID column?ALTER TABLE [Table_Name] ADD PRIMARY KEY (ID);
Which of the following statements will modify the data type of an already existing column?ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type; Alters the table by changing the datatype of column.
Which of the following SQL command you would use to list the columns with data types of a table?You can get the MySQL table columns data type with the help of “information_schema. columns”. SELECT DATA_TYPE from INFORMATION_SCHEMA. COLUMNS where table_schema = 'yourDatabaseName' and table_name = 'yourTableName'.
|