Which of the following queries will change the data type of an existing column phone to the varchar data type?

If you want to change all columns of a certain type to another type, you can generate queries using a query like this:

select distinct concat('alter table ', table_name, ' modify ', column_name, ' ', if(is_nullable = 'NO', ' NOT ', ''), ' NULL;') from information_schema.columns where table_schema = '' and column_type = '';

For instance, if you want to change columns from tinyint(4) to bit(1), run it like this:

select distinct concat('alter table ', table_name, ' modify ', column_name, ' bit(1) ', if(is_nullable = 'NO', ' NOT ', ''), ' NULL;') from information_schema.columns where table_schema = 'MyDatabase' and column_type = 'tinyint(4)';

and get an output like this:

alter table table1 modify finished bit(1) NOT NULL; alter table table2 modify canItBeTrue bit(1) NOT NULL; alter table table3 modify canBeNull bit(1) NULL;

!! Does not keep unique constraints, but should be easily fixed with another if-parameter to concat. I'll leave it up to the reader to implement that if needed..

Which of the following queries will change the data type of an existing column phone to the varchar data type?


This SQL tutorial explains how to use the SQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with lots of clear, concise examples). We've also added some practice exercises that you can try for yourself.

Description

The SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.

Add column in table

Syntax

To add a column in a table, the ALTER TABLE syntax in SQL is:

ALTER TABLE table_name ADD column_name column_definition;

Example

Let's look at a SQL ALTER TABLE example that adds a column.

For example:

ALTER TABLE supplier ADD supplier_name char(50);

This SQL ALTER TABLE example will add a column called supplier_name to the supplier table.

Add multiple columns in table

Syntax

To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);

Example

Let's look at SQL ALTER TABLE example that adds more than one column.

For example:

ALTER TABLE supplier ADD (supplier_name char(50), city char(45));

This SQL ALTER TABLE example will add two columns, supplier_name as a char(50) field and city as a char(45) field to the supplier table.

Modify column in table

Syntax

To modify a column in an existing table, the SQL ALTER TABLE syntax is:

For Oracle, MySQL, MariaDB:

ALTER TABLE table_name MODIFY column_name column_type;

For SQL Server:

ALTER TABLE table_name ALTER COLUMN column_name column_type;

For PostgreSQL:

ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition;

Example

Let's look at an example of how to modify a column called supplier_name using the ALTER TABLE statement. Note that most databases have a slightly different syntax.

For Oracle:

ALTER TABLE supplier MODIFY supplier_name char(100) NOT NULL;

For MySQL and MariaDB:

ALTER TABLE supplier MODIFY supplier_name VARCHAR(100) NOT NULL;

For SQL Server:

ALTER TABLE supplier ALTER COLUMN supplier_name VARCHAR(100) NOT NULL;

For PostgreSQL:

ALTER TABLE supplier ALTER COLUMN supplier_name TYPE CHAR(100), ALTER COLUMN supplier_name SET NOT NULL;

Modify multiple columns in table

Syntax

To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is:

For Oracle:

ALTER TABLE table_name MODIFY (column_1 column_type, column_2 column_type, ... column_n column_type);

For MySQL and MariaDB:

ALTER TABLE table_name MODIFY column_1 column_definition [ FIRST | AFTER column_name ], MODIFY column_2 column_definition [ FIRST | AFTER column_name ], ... ;

For PostgreSQL:

ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition, ALTER COLUMN column_name TYPE column_definition, ... ;

Example

Let's look at an example that uses the ALTER TABLE statement to modify more than one column. In this example, we will modify two columns called supplier_name and city.

For Oracle:

ALTER TABLE supplier MODIFY (supplier_name char(100) NOT NULL, city char(75));

For MySQL and MariaDB:

ALTER TABLE supplier MODIFY supplier_name VARCHAR(100) NOT NULL, MODIFY city VARCHAR(75);

For PostgreSQL:

ALTER TABLE supplier ALTER COLUMN supplier_name TYPE CHAR(100), ALTER COLUMN supplier_name SET NOT NULL, ALTER COLUMN city TYPE CHAR(75);

Drop column in table

Syntax

To drop a column in an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name DROP COLUMN column_name;

Example

Let's look at an example that drops (ie: deletes) a column from a table.

For example:

ALTER TABLE supplier DROP COLUMN supplier_name;

This SQL ALTER TABLE example will drop the column called supplier_name from the table called supplier.

Rename column in table

Syntax

To rename a column in an existing table, the SQL ALTER TABLE syntax is:

For Oracle and PostgreSQL:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'table_name.old_column', 'new_name', 'COLUMN';

For MySQL and MariaDB:

ALTER TABLE table_name CHANGE COLUMN old_name TO new_name;

Example

Let's look at an example that renames a column in the supplier table from supplier_name to sname.

For Oracle (9i Rel2 and up) and PostgreSQL:

ALTER TABLE supplier RENAME COLUMN supplier_name TO sname;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'supplier.supplier_name', 'sname', 'COLUMN';

For MySQL and MariaDB:

ALTER TABLE supplier CHANGE COLUMN supplier_name sname VARCHAR(100);

In MySQL and MariaDB, you must specify the data type of the column when you rename it.

Rename table

Syntax

To rename a table, the SQL ALTER TABLE syntax is:

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:

ALTER TABLE table_name RENAME TO new_table_name;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'table_name', 'new_table_name';

Example

Let's look at an example that renames a table called supplier to the new name vendor.

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:

ALTER TABLE supplier RENAME TO vendor;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'supplier', 'vendor';

Practice Exercise #1:

Based on the departments table below, rename the departments table to depts.

CREATE TABLE departments ( department_id int NOT NULL, department_name char(50) NOT NULL, CONSTRAINT departments_pk PRIMARY KEY (department_id) );

Solution for Practice Exercise #1:

The following SQL ALTER TABLE statement would rename the departments table to depts:

ALTER TABLE departments RENAME TO depts;

Practice Exercise #2:

Based on the employees table below, add a column called salary that is an int datatype.

CREATE TABLE employees ( employee_number int NOT NULL, employee_name char(50) NOT NULL, department_id int, CONSTRAINT employees_pk PRIMARY KEY (employee_number) );

Solution for Practice Exercise #2:

The following SQL ALTER TABLE statement would add a salary column to the employees table:

ALTER TABLE employees ADD salary int;

Practice Exercise #3:

Based on the customers table below, add two columns - one column called contact_name that is a char(50) datatype and one column called last_contacted that is a date datatype.

CREATE TABLE customers ( customer_id int NOT NULL, customer_name char(50) NOT NULL, address char(50), city char(50), state char(25), zip_code char(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );

Solution for Practice Exercise #3:

The following SQL ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:

ALTER TABLE customers ADD (contact_name char(50), last_contacted date);

Practice Exercise #4:

Based on the employees table below, change the employee_name column to a char(75) datatype.

CREATE TABLE employees ( employee_number int NOT NULL, employee_name char(50) NOT NULL, department_id int, CONSTRAINT employees_pk PRIMARY KEY (employee_number) );

Solution for Practice Exercise #4:

The following SQL ALTER TABLE statement would change the datatype for the employee_name column to char(75):

ALTER TABLE employees MODIFY employee_name char(75);

Practice Exercise #5:

Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a char(2) datatype.

CREATE TABLE customers ( customer_id int NOT NULL, customer_name char(50), address char(50), city char(50), state char(25), zip_code char(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );

Solution for Practice Exercise #5:

The following SQL ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:

ALTER TABLE customers MODIFY (customer_name char(50) NOT NULL, state char(2));

Practice Exercise #6:

Based on the employees table below, drop the salary column.

CREATE TABLE employees ( employee_number int NOT NULL, employee_name char(50) NOT NULL, department_id int, salary int, CONSTRAINT employees_pk PRIMARY KEY (employee_number) );

Solution for Practice Exercise #6:

The following SQL ALTER TABLE statement would drop the salary column from the employees table:

ALTER TABLE employees DROP COLUMN salary;

Practice Exercise #7:

Based on the departments table below, rename the department_name column to dept_name.

CREATE TABLE departments ( department_id int NOT NULL, department_name char(50) NOT NULL, CONSTRAINT departments_pk PRIMARY KEY (department_id) );

Solution for Practice Exercise #7:

The following SQL ALTER TABLE statement would rename the department_name column to dept_name in the departments table:

ALTER TABLE departments RENAME COLUMN department_name to dept_name;

Which of the following can be used in a SELECT statement to restrict a result set?

The LIMIT clause can restrict the result set of the query to some maximum number of rows.

Which of the following statement will add a column F_name to the student table?

Q7) Which of the following statement will add a column 'F_name' to the STUDENT table? ALTER TABLE command allows a user to add a new column to a table. Option B is correct syntax of ALTER to add a column in the table.

Which of the following are SQL databases?

SQL-compliant database server products include the following:.
Microsoft SQL Server..
Oracle Database..
IBM Db2..
SAP HANA..
SAP Adaptive Server..
Oracle MySQL..
open source PostgreSQL..

What is the purpose of a primary key coursera?

The main purpose of a primary key is to implement a relationship between two tables in a relational database; it's not called a relational database for nothing! More specifically, the primary key is the "target" which a foreign key can reference.