Having created the tables and entering data into them, now you want to extract some information. That’s when you query the database. As the name suggests, query is to collect specific information from the pool of data. A query helps us join information from different tables and filter that information. Filtering means that the query uses criteria you provide it to hide some data and present only what you want to see. Some RDBMS provide a graphical means to create queries, but most RDBMS do not do so. That’s where you use SQL (pronounced as “sequel”) or Structured Query Language. Query languages are computer languages used to make queries into databases and information systems. Queries are commands that are used to define the data structure and also to manipulate the data in the database.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language (DML) command.
The SELECT statement has many optional clauses:
- WHERE specifies which rows to retrieve.
- ORDER BY specifies an order in which to return the rows.
In order to execute queries click on the Queries option available on the left side under
database section, click Create Query in SQL View as shown below.
A window appears similar to the one displayed below.
You can type the query in the above window and execute it by using the F5 function key or by clicking the
icon in the window.
For example, if you want to display all the data in the table that you created in the early session, then the select statement will be: select * from SDetails;
After executing the select query the output will be shown similar to the one displayed below.
To get details about the list of students whose favorite color is blue, you can use:
select * from SDetails where Color='Blue'.
After executing the select query the output will be shown similar to the one displayed below.
To view records in ascending order of RollNo, from the table the select statement will be:
select * from "SDetails" order by "Rollno" ASC;
You can add, modify or delete records using the Insert, Update and Delete commands.
To type and execute SQL commands, click on Tools > SQL. A window similar to the one below will be displayed.
You can type the SQL Commands in the Command to execute space and click on Execute.
INSERT statement
INSERT statement is used to add one or more records to a database. The general syntax of the insert statement is shown below.
INSERT INTO table (column1, column2, column3 ...) VALUES (value1, value2, value3 ...)
To add a record in the database created earlier, type the following and click Execute.
insert into "SDetails" ("ID", "Name", "Rollno", "DOB", "Class" , "Phone", "Email", "Color", "Location") values ('8', ' Ranjith Singh',' 67' , '12-03-99','X' , '435363' , ' ranjth99@gmail.com' , 'White', 'Bihar');
After inserting the data into the table, use select query to view the updated table. After execution you should see a window similar to the one displayed below.
UPDATE statement
Update statement is used for modifying records in a database. The general syntax of the update statement is as follows:
UPDATE table_name SET column_name = value [, column_name = value ...] [WHERE condition]
To update a record using update statement, type the following and click Execute.
update "SDetails” set "Location"='Bhubaneswar' where "Rollno"=14;
Execute select query to view the updated table. After execution you should see a window similar to the one displayed below.
DELETE statement
Delete Statement is used to remove one or more records in a database. The general syntax of the delete statement is as follows:
DELETE FROM “table_name” [WHERE] condition;
To delete one of the records in the table created earlier using delete statement, type the
following and click Execute:
delete from "SDetails" where ID=8;
Execute select query to view the updated table. After execution you should see a window similar to the one displayed below.
Notice the record with the Roll No 8 is deleted from the database.
CREATE Statement
Create statement is used for creating a database or a table in any RDBMS Software. A commonly used CREATE command is the CREATE TABLE command. The general syntax of the create statement is shown below.
CREATE TABLE TABLENAME ([column definitions]) [table parameters]
Column definitions: A comma-separated list consisting of any of the following
Column definition: [column name] [data type] {NULL | NOT NULL} {column options}Primary key definition: PRIMARY KEY ([comma separated column list])
For example, if you would like to create a table using the Create statement, type the
following and click Execute.
CREATE TABLE "Employee" ("ID" INTEGER," Name "VARCHAR (50),
"Department" VARCHAR (50),
"Address" VARCHAR (120),"Contact Number" INTEGER);
Now create 5 records in the table and use the SQL statements to view, modify and delete them.
0 comments:
Post a Comment