Copyright

Data Definition Language (DDL) Commands

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: Data Manipulation Language (DML) Commands

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:03 Tweaking the Structure
  • 0:30 The CREATE Command
  • 1:13 The DROPCommand
  • 1:34 The ALTER Command
  • 3:56 Lesson Summary
Add to Add to Add to

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Login or Sign up

Timeline
Autoplay
Autoplay

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

Data definition language (DDL) is a unique set of SQL commands that lets you manipulate the structure of the database. In this lesson, we will explore these commands and see them in action.

Tweaking the Structure

It might sound like its own programming language, but data definition language (DDL) is really a way to view certain SQL commands. These are commands that are used to modify the structure of a database, rather than the database itself (the categorization of those commands is called data manipulation language). We'll take a look at the some of the major commands in DDL. These include CREATE, DROP, and ALTER.

The CREATE Command

The CREATE command is used to create a table. Remember that we are dealing with the structure of the database, and so we'll not be inserting any data into the table; the command simply builds the table for use, like this:


CREATE TABLE syntax


The command requires a table name and at least one column with its corresponding data type (e.g., text, numeric, and so on). In SQL Server, there is an option to specify a primary key and/or require that a field not be null.

And if we want to see how this would look in practice, let's create a table for a music database. We'll create an artist table, like this:


CREATE TABLE Example


We've set a primary key (artistID) and ensured that some fields will not be null, or blank.

The DROP Command

The DROP command is used to drop a table from the database. When dropped, all the data goes with it; however, for this lesson we are only concerned with tweaking the structure.

The syntax for the command is quite simple, but very powerful, as we see here:


DROP Syntax


Now, if we want to drop our artist table, these statements can be used:


DROP Example


The ALTER command

The DROP command is quite extreme, as it completely wipes out the table and any data in it. When we are first building the structure of the database through DDL commands, this is not necessarily bad. However, once data exists in the tables of our database, modifying the structure is easier through other means, such as ALTER. The ALTER command is used to add, change, or remove columns and/or fields in the table. It can also be used to rename the table.

Let's break this one down a little and look at each option: adding a column(s), modifying column(s), removing columns, and renaming.

Add Column(s)

In order to add a new column, the ALTER command requires syntax similar to the CREATE statement. The table name is required and so are the column names/definitions. We'll first see how we might add some new columns to our artist database. In this example, we'll add a sub-genre (think folk or rock) and a state code (we added country, so let's add state or province) like this:


ALTER Add Columns


Modify a Column

Now, we will modify the artist table to increase the size of the artist's name and require a genre. The command requires the table name, the column names, and column type, like this:


ALTER Modify Columns


To unlock this lesson you must be a Study.com Member.
Create your account

Register for a free trial

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Free 5-day trial

Earning College Credit

Did you know… We have over 160 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it free for 5 days!
Create an account
Support