How to create a basic SQL table?

The CREATE TABLE statement creates a new base table with all corresponding columns and their data types. The basic form of the CREATE TABLE statement is: CREATE TABLE table_name (col_name1 type1 [NOT NULL| NULL] [{, col_name2 type2 [NOT NULL| NULL]} …]) table_name is the name of the created base table. The maximum number of tables per database is limited by the number of objects in the database (there can be more than 2 billion objects in a database, including tables, views, stored procedures, triggers, and constraints). col_name1, col_name2,… are the names of the table columns. type1, type2,… are data types of corresponding columns. If NOT NULL is specified, the assignment of NULL values for the column is not allowed. (In that case, the column may not contain nulls, and if there is a NULL value to be inserted, the system returns an error message.) As already stated, a database object (in this case, a table) is always created within a schema of a database. A user can create a table only in a schema for which she has ALTER permissions. Any user in the sysadmin, db_ddladmin, or db_owner role can create a table in any schema. The creator of a table must not be its owner. This means that you can create a table that belongs to someone else. Similarly, a table created with the CREATE TABLE statement must not belong to the current database if some other (existing) database name, together with the schema name, is specified as the prefix of the table name. Example SQL Query: USE PhiBui; CREATE TABLE employee (emp_no INTEGER NOT NULL, emp_fname CHAR(20) NOT NULL, emp_lname CHAR(20) NOT NULL, dept_no CHAR(4) NULL); CREATE TABLE department(dept_no CHAR(4) NOT NULL, dept_name CHAR(25) NOT NULL, location CHAR(30) NULL); CREATE TABLE project (project_no CHAR(4) NOT NULL, project_name CHAR(15) NOT NULL, budget FLOAT NULL); CREATE TABLE works_on (emp_no INTEGER NOT NULL, project_no CHAR(4) NOT NULL, job CHAR (15) NULL, enter_date DATE NULL); The DEFAULT clause in the column definition specifies the default value of the column that is, whenever a new row is inserted into the table, the default value for the particular column will be used if there is no value specified for it. A constant value, such as the system functions USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_TIMESTAMP, and NULL, among others, can be used as the default values. A column with the IDENTITY property allows only integer values, which are usually implicitly assigned by the system. Each value, which should be inserted in the column, is calculated by incrementing the last inserted value of the column. Therefore, the definition of a column with the IDENTITY property contains (implicitly or explicitly) an initial value and an increment. Learn more:


<<  July 2021  >>

View posts in large calendar