Explore topic-wise InterviewSolutions in Current Affairs.

1. record

2. Data Defnition Language

Answer is (d) ALTER

Queries can be generated using SELECT

alter command changes the structure of the database

HAVING clause: 

The HAVING clause can be used along with GROUP BY clause in the SELECT statement to place condition on groups and can include aggregate functions on them.

For example to count the number of Male and Female students belonging to Chennai.

SELECT gender, COUNT(*) FROM Student GROUP BY Gender HAVING Place = ‘Chennai’;

The above output shows the number of Male and Female students in Chennai from the table student.

(c) removes individual fields

Answer is alter

The command to delete a table is DROP

The betweeen keyword in select command includes an upper value and a lower value.

delete is not a SQL DDL command

(a) 1-ii, 2-iii, 3-i, 4-iv

CREATETABLE is a DDL command.

Creating Database

(i) To create a database, type the following command in the prompt:

CREATE DATABASE database_name;

For example to create a database to store the tables:

CREATE DATABASE stud;

(ii) To work with the database, type the following command

USE DATABASE;

For example to use the stud database created, give the command

USE stud;

DELETE COMMAND

The DELETE command permanently removes one or more records from the table. It removes the entire row, not individual fields of the row, so no field argument is needed.

The DELETE command is used as follows :

DELETE FROM table-name WHERE condition; For example to delete the record whose admission number is 104 the command is given as follows: DELETE FROM Student WHERE Admno=104;

The following record is deleted from the Student table.

To delete all the rows of the table, the. command is used as :

DELETE * FROM Student;

The table will be empty now and could be destroyed using the DROP command.

BETWEEN and NOT BETWEEN Keywords

The BETWEEN keyword defies a range of values the record must fall into to make the condition true. The range may include an upper value and a lower value between which the criteria must fall into. 

SELECT Admno, Name, Age, Gender FROM Student WHERE Age BETWEEN 18 AND 19;

The NOT BETWEEN is reverse of the BETWEEN operator where the records not satisfying the condition are displayed.

SELECT Admno, Name, Age FROM Student WHERE Age NOT BETWEEN 18 AND 19;

There are 5 components of SQL

Keywords They have a special meaning in SQL. They are understood as instructions.

Commands They are instructions given by the user to the database also known as statements. Clauses They begin with a keyword and consist of keyword and argument.

Arguments They are the values given to make the clause complete.

A DDL performs the following functions :

1. It should identify the type of data division such as data item, segment, record and database file.

2. It gives a unique name to each data item type, record type, file type and data base.

3. It should specify the proper data type.

4. It should define the size of the data item.

5. It may define the range of values that a data item may use.

6. It may specify privacy locks for preventing unauthorized data entry.

DELETE, TRUNCATE AND DROP statement: The DELETE command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table. The TRUNCATE command is used to delete all the rows, the structure remains in the table and free the space containing the table. 

The DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back.

SQL command which come under Transfer Control Language are:

Transaction Control Language- component of SQL includes commands for specifying transactions.

DML COMMANDS

Once the schema or structure of the table is created, values can be added to the table. The DML commands consist of inserting, deleting and updating rows into the table.

(i) INSERT command

The INSERT command helps to add new data to the database or add new records to the table.

Syntax: INSERT INTO [column-list] VALUES (values);

(a) INSER T INTO Student (Admno, Name, Gender, Age, Place)

VALUES (100, ‘Ashish ’ , ‘M\ 17, ‘Chennai);

(b) INSERT INTO Student (Admno, Name, Gender, Age, Place)

VALUES (10, ‘Adarsh’ , ‘M’ , 18, ‘Delhi);

(c) INSERT INTO Student VALUES (102, ‘Akshith \ ‘M’ , ‘17, ’ ‘Bangalore);

The above command inserts the record into the student table.

To add data to only some columns in a record by specifying the column name and their data, it can be done by:

(d) INSERT INTO Student(Admno, Name, Place) VALUES (103, ‘Ayush’ , ‘Delhi’);

(e) INSERT INTO Student (Admno, Name, Place) VALUES (104, ‘Abinandh ‘Chennai);

The student table will have the following data:

(ii) DELETE COMMAND

The DELETE command permanently removes one or more records from the table. It removes the entire row, not individual fields of the row, so no field argument is needed.

The DELETE command is used as follows:

DELETE FROM table-name WHERE condition; For example to delete the record whose admission number is 104 the command is given as follows: DELETE FROM Student WHERE Admno=104;

The following record is deleted from the Student table. To delete all the rows of the table, the command is used as :

DELETE * FROM Student;

The table will be empty now and could be destroyed using the DROP command.

(iii) UPDATE COMMAND

The UPDATE command updates some or all data values in a database. It can update one or more records in a table. The UPDATE command specifies the rows to be changed using the WHERE clause and the new data using the SET keyword. The command is used as follows: UPDATE SET columnname = value, column-name = value,… WHERE condition;

For example to update the following fields:

UPDATE Student SET Age = 20 WHERE Place = “Bangalore ”;

The above command will change the age to 20 for those students whose place is “Bangalore”.

The table will be as updated as below:

To update multiple fields, multiple field assignment can be specified with the SET clause separated by comma. For example to update multiple fields in the Student table, the command is given as: UPDATE Student SETAge=18, Place = ‘Chennai’ WHERE Admno = 102;

The above command modifies the record in the following way.

DATE is odd from the above

False. (without double quotes i.e. null is then it is true.)

Table details are stored in this file.

INSERT INTO CUSTOMER VALUES(1001, ‘ALVIS’, 9447024365, NULL);

(a) varchar 

(b) date 

(c) decimal

Data Definition Language(DDL) – It is used to define the structure of a table.

Data Definition Language is used to specify the definitions of Database Schema. The result of the compilation of DDL statements is a set of tables stored in a special file called data dictionary.

DDL Commands are Create table, Alter table and Drop table.

Data Manipulation Language(DML) – It is used to add, retrieve, modify and delete records in a data base. It is a language that enable users to access or manipulate data in the database. It also provides interfaces with programming languages.

DML Commands are select, insert, update and delete

(c) Delete From Customer

INSERT INTO CUSTOMER VALUES(1001, ‘ALVIS’, 9447024365, NULL);

In this query Group is the keyword hence it cannot be used. The correct query is as follows. 

SELECT * FROM STUDENT WHERE Marks > 50 ORDER BY Marks;

Ip Yes. DATE data type

DROP TABLE is used to remove a table from a database

(a) Select distinct sub code from mark; 

(b) Select marks from mark where sub Code= 3001 or sub Code = 3002; 

(c) Select * from mark order by sub code, marks; 

(d) Select * from mark where sub code in (3001,3002) and marks >90; 

(e) Select *from mark order by marks desc;

1. Char – It is used to store fixed number of characters. It is declared as char (size)

2. Varchar – It is used to store characters but it uses only enough memory.

1. name varchar (25) 

2. Because char data type is fixed length. It allocates maximum memory i.e, here it allocates memory for 25 characters maybe there is a chance of memory wastage. But Varchar allocates only enough memory to store the actual size.

1. Select distinct batch from Student; 

2. Select name, batch from Student where percent is null 

3. 5

USE command is used to open a database.

DISTINCT is the keyword used with SELECT command to avoid duplication of rows in the selection.

Column constraints are specified while defining each column, table constraints are specified once for the entire table at the end of table definition.

& symbol is used as substitution operator in SQL.

Alter table command is used to modify existing column or add new column to an existing table.

There are 2 keywords used ADD and MODIFY.

We can alter the table in two ways.

We can add a new column to the existing table using the following syntax,

ALTER TABLE

<tablename>ADD(<cloumnname><type><constraint>);

We can also cha rige or modify the existing column in terms of type or size using the following syntax,

ALTER TABLE<tablename>MODIFY(<column><newtype>);

We can alter the table in two ways. 

• We can add a new column to the existing table using the following 

syntax, 

ALTER TABLE

<tablename>ADDD(<cloumnname><type><constraint>); 

• We can also change or modify the existing column in terms of type or size using the following 

syntax, 

ALTER TABLE 

<tablename>MODIFY(<column><newtype>);

Tables and views are different. A view is a single virtual table that is derived from other physically existing tables. When we access a view we actually access the base tables.

We can use all the DML commands with the views but care should be taken as operation actually reflects in the base tables. The advantage of view is that without sparing extra storage space, we can use same table as different virtual tables. It also implements sharing along with privacy).

(c) UPDATE. 

It is a command and others are clauses.

Pattern matching can be done using the operator LIKE while setting the condition with pattern matching. for eg., to display the names of all students whose name begins with letters ‘ma’, we can write the following query, SELECT name FROM STUDENT WHERE name LIKE ‘ma%’; here the character ‘%’ – substitutes any number of characters in the value of the specified column. Another character substitutes only one character of the specified column.

& symbol is used as substitution operator in SQL

Unique – It ensures that no two rows have the same value in a column.

Primary key – Similar to unique but it can be used only once in a table.

The strings (i) and (iv) only

Count() – find the number of non null values in a column. 

Count(*) This is used to find the number of records with at least one field.

First generation computers were vacuum tubes based machines. These were large in size, difficult to operate and instructions were to be written in machine language. Their computation time was in milliseconds.