Basic SQL Commands in Database Management Systems (DBMS)

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: ACID Properties in Data Base Management Systems (DBMS)

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 DBMS & SQL
  • 1:12 Basic SQL Commands
  • 2:58 SELECT SQL Statements
  • 4:58 JOIN SQL Statements
  • 6:20 UPDATE, INSERT, &…
  • 8:43 Lesson Summary
Save Save Save

Want to watch this again later?

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

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Katherine Wenger

Kate has a Bachelors, Masters, and is a Ph.D. candidate in the fields of Information Technology and loves teaching students

Basic SQL commands allow the user to achieve significant manipulation of data in the database. The database management system is the software that allows access to the database and to apply SQL commands to manage data.

DBMS & SQL

In this lesson, we'll go over basic Structured Query Language (SQL) commands for accessing and managing data in a database management system (DBMS). A DBMS is the software that allows you access and manipulate the data found within the database. SQL is the database language used to perform a query, which is simply an inquiry, of the database.

One way to think of how SQL works is by comparing it to ordering a pizza delivery. For example, first you make a phone call to the pizza restaurant which can be compared to logging on to the DBMS; next, you request pepperoni and onions as your pizza toppings which can be compared to a entering a SQL query in the DBMS; finally, the pizza is delivered to your house which can be compared to the retrieval of data requested by the SQL query in the DBMS.

Essentially, entering SQL commands in a DBMS is a three-step process which includes:

  1. Gaining access to the DBMS
  2. Requesting specific data to be returned from the DBMS
  3. The DBMS providing the specifically indicated data to the user

Basic SQL Commands

SQL commands are used to access and alter data found within the tables within a database or to the database directly. Assuming that tables within a database exist, we'll discuss several basic SQL commands. We will build upon and alter an example table named Employee and another named Department in consecutive steps using SQL commands.

The Employee table is constructed as follows:

last_name first_name hire_date department_number ID
Jones Michael 1/5/2000 10 213
Williams Michelle 11/27/2001 09 208
Smith David 9/15/2002 10 205
Miller Cary 05/29/2001 09 209
Davis Anne 03/11/2002 09 204
Wilson Cooper 09/17/2000 11 201


The Department table is constructed as follows:

department_name department_number
Accounting 11
Shipping 10
Receiving 09


SELECT SQL Statements

The SELECT command constitutes the first line in a SQL query intended to gather data from a table. The options in a SELECT command include retrieving either all the rows and columns from the table, or designating which rows, columns, or other parameters of data are to be retrieved. The SELECT * command gathers all the rows and columns in a table.

This returns all the rows and columns in the Employee table. The FROM command designates which table the data should come from. To be more selective, you can select certain columns, under specific conditions, and even order the results according to specific parameters. An example of applying parameters to a query looks like this:


SELECT last_name, hire_date

FROM employees

WHERE hire_date <= 12/1/2001

AND department_number = 09

ORDER BY hire_date;


This will display like this:

last_name hire_date
Miller 05/29/2001
Williams 11/27/2001


In the example above, we indicated that we wanted the results columns to include only the last name and hire date of employees who were hired before 12/1/2001 and work in department number 09 and ordered chronologically by hire date. Expanding upon the WHERE clause is written, you could use the LIKE clause. The LIKE clause can specify more specific parameters, like this:


WHERE first_name LIKE 'm%'


Will return all employees in the Employee table whose name begins with the letter M, which would be Michelle Williams in the example. A benefit of setting parameters in a SQL query is when you want only specific data returned.

JOIN SQL Statements

A JOIN SQL statement combines data from two tables based on specified conditions. As with the SELECT statement, there are a number of ways to manipulate data with a JOIN statement. The syntax for a JOIN statement builds upon a SELECT statement syntax, which will become apparent through the following example:

Suppose we want to retrieve a list of employees and their corresponding employee ID, who work in the shipping department, we would use this command:


SELECT last_name, id

FROM employee

INNER JOIN department

ON employee.department_number = department.department_number

WHERE department_number = 10;


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

Register to view this lesson

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
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 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 risk-free for 30 days!
Create an account
Support