SQL Data Definition: CREATE DATABASE, CREATE TABLE, ALTER and DROP

Hello students, welcome to Kwickprep. One short command can delete a whole school's data in less than a second, with no undo. Which command is it? Today we will create a database and a table with rules, change the table with alter, and learn why drop is so dangerous.

A database is like a cupboard that holds many tables. Create database school makes a new, empty database called school. Show databases lists every database, so you can check that school is there. Use school tells My S Q L that the next commands are for this database. Show tables lists its tables, and right now there are none.

Students often forget one thing about use. Creating a database does not automatically select it. If you skip use and create a table, My S Q L gives the error, no database selected. And every time you log in again, you must run use once more.

A constraint is a rule that a column must follow, and the database checks it on every row. Primary key means the value is unique and never empty. Not null means the value can never be left empty, and null means no value. Unique means no two rows can have the same value, but an empty value is allowed. Default gives a ready value when the user does not supply one.

Here is the student table with all four constraints. Each column has a name, a data type, and then its constraint. Roll is an int and the primary key. Name is var char of thirty and not null, so every student must have a name. Email is unique, so two students cannot share one. City has a default of Surat. Commas separate the columns, and the brackets close with a semicolon.

Let us test the rules with one row. We insert roll one and the name Riya, but we give no city. Pause and predict. What city will Riya get? The output shows Surat, because the default filled it in. If we now insert roll one again for Aman, My S Q L refuses with a duplicate entry error for the primary key.

Alter table changes the structure of a table that already exists, without losing its rows. Add phone char of ten adds a new column at the end, and old rows get null in it. Modify changes a column's data type or size, so name can now hold fifty characters. Be careful, modify replaces the whole column definition. If we leave out not null here, the name column quietly loses that rule.

Drop column phone removes the column and every value stored in it. Next, suppose we made a marks table and forgot the primary key. Alter table marks, add primary key, roll, adds it later. This works only if roll has no repeated values and no empty values. Otherwise, My S Q L refuses to add the key.

How do we check that alter worked? Desc student, short for describe, shows every column and its rules. The output here is shortened to fit the screen. Roll is the primary key, and name is now var char of fifty and cannot be null. Email is unique, city keeps its default of Surat, and the phone column is gone.

Now the most powerful commands. Drop table marks deletes the marks table completely, its structure and all its rows. Drop database school deletes the entire database, with every table inside it. Both run instantly, and My S Q L does not ask, are you sure?

Here is why drop is so dangerous. In My S Q L, D D L commands are saved at once, so there is no undo. Drop database wipes every table inside it, like a whole year of marks and fees. So always take a backup first, and read the command twice before pressing enter. Also, do not confuse the two: delete removes rows but keeps the table, while drop removes the table itself.

Let us revise what we learned today. Create database makes a database, and use selects it. Create table lists each column with its name, data type and constraint. Primary key, not null, unique and default are the four rules we used. Alter table can add, modify or drop a column, and add a primary key later. Drop table and drop database cannot be undone, so back up first.