Creating and Dropping a table

A table is an object which stored data in rows and columns.

The table creation command requires the following details: Name of the table Name of the fields Definitions for each field

Sample Basic Syntax:

create table table_name (
  column_name1 column_type1 [default <<values>>]
  ,column_name1 column_type1 [default <<values>>]
 );

Full Syntax:

https://dev.mysql.com/doc/refman/8.0/en/create-table.html

Example:

Let us create a table named deptt_<<your lab username>> with fields like depid, depname, depopendate.

%%sql

use retail_db;

DROP TABLE IF EXISTS deptt_<< your lab username >> ;

create table deptt_<< your lab username >> (
 depid int not null,
 depname varchar(30) not null default 'New',
 depopendate datetime not null default CURRENT_TIMESTAMP
);

Try creating above table and if succeeds, you will see a message like below one, else you will see relevant error message: Query OK, 0 rows affected (0.01 sec)

View the table definition using below commands:

%%sql
describe deptt_<< your lab username >> ;

%%sql
show create table deptt_<< your lab username >> ;

%%sql
show columns from deptt_<< your lab username >> ;

Try selecting the data from the table. You would see 0 rows because no data has been inserted yet into the table.

%%sql
select * from deptt_<< your lab username >> ;

Dropping a table Tables can be dropped using 'drop table' command. A table can not be dropped if it is being used by someone else or if the table has child tables enforced by Foreign Keys and child table has records.

%%sql
drop table deptt_<< your lab username >> ;

Previous Index Next

SQL with Jupyter Notebook Tutorial

Creating and Dropping a table

XP

Loading comments...