How to perform DBMS Lab and its methodologies

DBMS LAB

The main objective of this lab is to gain knowledge of the select statement that is used SQL

Querying MySQL tables

Data is extracted from the table using the SELECT SQL command. Here is the format of a SELECT statement:

SELECT column_names from table_name [WHERE ...conditions];

The conditions part of the statement is optional. Basically, you require to know the column names and the table name from which to extract the data.

SELECT f_name, l_name from employee_data;

The statement tells MySQL to list all the rows from columns f_name and l_name.
The display is in the order in which the data was inserted. Furthermore, the last line indicates the number of rows our table has.
To display the entire table, we can either enter all the column names or use a simpler form of the SELECT statement.

SELECT * from employee_data;

Some of you might recognize the * in the above statement as the wildcard. Though we don't use that term for the character here, it serves a very similar function. The * means 'ALL columns'. Thus, the above statement lists all the rows of all columns.
ASSIGNMENT 1

1. Write the complete SQL statement for creating a new database called addressbook


2. Which statement is used to list the information about a table? How do you use this statement?


3. How would you list all the databases available on the system?


4. Write the statement for inserting the following data in employee_data table
      First name: Rudolf
      Last name: Reindeer
      Title: Business Analyst
      Age: 34
      Years of service: 2
      Salary: 95000
      Perks: 17000
      email: rudolf@bugnet.com


5. Give two forms of the SELECT statement that will list all the data in employee_data table.


6. What will select f_name, email from employee_data; display?


7. Write the statement for listing data from salary, perks and yos columns of employee_data table.


8. How can you find the number of rows in a table using the SELECT statement?


9. What will select salary, l_name from employee_data; display?

 

selecting data using conditions

We will learn how to use the select statement using the WHERE clause.

SELECT column_names from table_name [WHERE ...conditions];

Now, we know that the conditions are optional

The SELECT statement without conditions lists all the data in the specified columns. The strength of RDBMS lies in letting you retrieve data based on certain specified conditions.
 Comparision Operators of  SQL

                        The = and != are the  comparision operators

Examples of using comparision operators

1)SELECT f_name, l_name from employee_data where f_name =’john';

This query displays the first and last names of all employees whose first names are John.

2) SELECT f_name,l_name from employee_data where title="Programmer";

This query display the first and last names of all employees who are programmers.

3) SELECT f_name, l_name from employee_data where age = 32;

This lists the first and last names of all empoyees 32 years of age. Remember that the column type of age was int, hence it's not necessary to surround 32 with quotes

The != means 'not equal to' and is the opposite of the equality operator

The greater than and lesser than operators(< and >)

Examples

1) SELECT f_name, l_name from employee_data where age > 32;

  This query display the first name and the last name of all the employee who are greater that 32 years

2) SELECT f_name, l_name from employee_data where salary > 120000;

  This query display the first name and the last name of all the employee who  who draw more than $120000 as salary

3) SELECT f_name, l_name from employee_data where yos < 3;

  This query display the first name and the last name of all the employee who had less than 3 year of service in the company

The <= and >= operators

These operators are used primarily with integer data, the less than equal (<=) and greater than equal (>=)operators provide additional functionality.

Examples

1) select f_name, l_name, age, salary

from employee_data where age >= 33;

This query display the names, ages and salaries of employees who are more than or equal to 33 years of age.

2) select f_name, l_name from employee_data where yos <= 2;

This query displays employee names who have less than or equal to 2 years of service in the company.
ASSIGNMENT 2

1. Write the SELECT statement to extract the ids of employees who are more than 30 years of age.


2. Write the SELECT statement to extract the first and last names of all web designers.


3. What will the following SELECT statement display:

4. SELECT * from employee_data where salary <=100000;

5. How will you display the salaries and perks for employees who have more than $16000 as perks?


6. List all employee names (last name followed by first name) who hold the title of Marketing Executive.