In this lesson, we’ll explore the select statement. The select statement is used mostly for reading data from objects in the database.
Let’s create a table, insert data into it and display the data using the select statement.
—Creating the table
CREATE TABLE student
(sid int primary key,
firstname varchar(50),
lastname varchar (50)
)
–Adding records to the table
INSERT INTO student
VALUES (1,’John’,’Edward’),
(2,’John’,’Mccain’),
(3,’JJ’,’Rawlings’)
–Displaying all the records
SELECT *
FROM student
This will display the details of all students in the table students.
SELECT * means retrieve all columns from the table.
If l want to retrieve only some specific columns, then l have to specify those column in the select statement.
–For example, Display only first name and last name of all students
SELECT firstname, lastname
FROM student
The last version of the select statement is called the select into statement. Select into creates a table and dump all selected records into it. It’s like backing up one table with a new table. The new table is created in the same schema as the table from which the records are selected. It usually, use the three name convention to specify the table in the select statement. The 3 part naming conversion(database.schema.table) is necessary because the new table will be created with the schema for the existing table. I’ll talk about schemas later on. Lets not chew more than we can bite. But schemas are just containers for other objects. To create a schema, you simple do it like this,
CREATE SCHEMA schemaname. The hierarchy of a server structure looks like this
server->database->schema->objects
the objects are the tables, views, stored procedures etc. we’ll look at most of the objects during the course of the entire tutorial.
Lets now copy the structure and content of a student table and create a new table called student_backup and dump everything into it. This is done like this,
SELECT sid, firstname, lastname
INTO student_backup
FROM databasename.schema.student
Now lets just copy the structure of the student table for the back up table without dumping the data from the student table into the backup table.
SELECT sid, firstname, lastname
INTO dabasename.schema.student_backup
FROM databsename.schema.student
where 1 = 0
Ok, another thing we’ll look at is the WHERE clause. It’s for giving conditions that have to be met. As we can see above, the condition following the WHERE statement is never true so no data will be populated in the new table but the structure will be copied for the new table with no records. You can put any condition in the WHERE statement that will give a false answer all the time to achieve this task. We’ll talk more about where in the following tutorials. Questions, feedbacks welcome, just drop a comment.
