1] What is the difference between delete and truncate statements? Delete command • Delete command is used to delete a row in a table • You can rollback data after using delete command • It’s a DML command • Its slower than truncate statement Truncate command • Truncate is used to delete all the rows from a table • You cannot rollback data • It’s a DDL command • Its faster 2] What do you mean by DBMS? What are its different types? A database management system is a software application that interacts with the user, Application and the database itself to capture and analyse data.The data stored in the Database can be modified, retrieved and deleted and can be of any type like strings, Numbers, images etc. 3] What is the difference between SQL and MySQL? SQL It’s a standard language which stands for structured query language based on the English Language. SQL is a core relational database which is used for accessing and managing Database. MySQL It’s an open source relational database management system that works on many platforms. It provides multi user access to support many storage engines and is backed by oracle. 4] What is a foreign key? Foreign key maintains referential integrity by enforcing a link between the data in two tables. The foreign key in the child table references the primary key in the parent table. The foreign key Constraint prevents actions that would destroy links between the child and parent tables. 5] What are the different types of joins? Inner join: this join returns those records which have matching values in both the tables. Full join: this join return all those records which either have a match in the left or right table. Left join: this join returns records from all the left table, and also those records which satisfy the Condition from the right table. Right join: this join returns records from the right table, and also those records which satisfy the Condition from the left table. 6] What do you mean by denormalization? It refers to a technique which is used to access data from higher to lower forms of a database. Increase the performance of the entire infrastructure as it introduces redundancy into a Table. Adds the redundant data into a table by incorporating database queries that combine Data from various tables into a single table. 7] What are entities and relationships? Entities: a person, place or a thing in the real world about which data can be stored in a database. Table’s stores data that represents one type of entity. Relationship: relation or links that have something to do with each other. 8] What is normalisation and what are its advantages? Normalization is the process of organizing data to avoid duplication and redundancy. Advantages: • Bette database organization • More tables with smaller rows • Efficient data access • Greater flexibility for queries • Quickly find the information • Easier to implement security • Allows easy modification • Ensure consistent data after modification 9] What do you mean by trigger in SQL? trigger in SQL are a special type of stored procedures that are defined to execute automatically In place or after data modifications. It allows you to execute a batch of code when an insert, Update or any other query is executed against a specific table. 10] Are null values same as that of zero or blank space? a null value is not at all same as that of zero or a blank space. Null value represents a value Which is unavailable, unknown, assigned or not applicable whereas a zero is a number and blank Space is a character. 11] What is the difference between cross join and natural join? Cross join produces the cross product or Cartesian product of two tables. Natural join: based on all the columns having the same name and the data types. 12] What is sub query in SQL? A sub query is a query inside another query where a query is defined to retrieve data or information Back from the database. Sub queries are always executed first and the result of the sub query is passed on to the main query. 13] What are different types of SQL statements? • DDL-data definition language • DML-data manipulation language • DCL-data control language • TCL-transaction control language 14] What is a view? A view is a virtual table which consist of a subset of data contained in a table. Views are not virtually present, and it takes less space to store. View can have data of one or more tables Combined, and it is depending on the relationship. 15] What is an index? An index is performance turning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data. 16] What is a stored procedure? Stored procedure is a function consists of many SQL statement to access the database system Several SQL statements are consolidated into a stored procedure and execute them whenever And wherever required. 17] What is data warehouse? Data warehouse is a central repository of data from multiple sources of information. Those Data are consolidated, transformed and made available for the mining and online processing . Ware house data have a subset of data called data marts. 18] What is user defined functions? User defined functions are the functions written to use that logic whenever required. It’s not necessary to write the same logic several times. Instead function can be called Or executed whenever required. 19] What is an ALIAS command? ALIAS name can be given to a table or column. This alias name can be referred in Where Clause to identify the table or column. 20] What are aggregate and scalar functions? Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in the table. Scalar functions return a single value Based on the input value. 21] What is SQL? Structured Query Language is a database tool which is used to create and access database to support software application 22] What are properties of the transaction? Properties of transaction are known as ACID properties, such as Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully if not then transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes undone Consistency: Ensures that all changes made through successful transaction are reflected properly on database Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on other Durability: Ensures that the changes made in database with committed transactions persist as it is even after system failure 23] What is SQL Injection? SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is usually used for attacking Data-Driven Applications to have an access to sensitive data and perform administrative tasks on databases. 24] What is the use of NVL function? NVL function is used to convert the null value to its actual value. 25] What is the difference between clustered and non-clustered indexes? One table can have only one clustered index but multiple nonclustered indexes. Clustered indexes can be read rapidly rather than non-clustered indexes. Clustered indexes store data physically in the table or view and non-clustered indexes do not store data in table as it has separate structure from data row 26] What is the difference between DROP and TRUNCATE? TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it cannot be retrieved back. 27] State some properties of Relational databases? In relational databases, each column should have a unique name The sequence of rows and columns in relational databases are insignificant All values are atomic and each row is unique 28] What is a Cursor? A cursor is a database object which is used to manipulate data in a row-to-row manner. Cursor follows steps as given below Declare Cursor Open Cursor Retrieve row from the Cursor Process the row Close Cursor Deallocate Cursor 29] What is Collation? Collation is a set of rules that check how the data is sorted by comparing it. Such as Character data is stored using correct character sequence along with case sensitivity, type, and accent. 30] How to select all records from the table? To select all the records from the table we need to use the following syntax: Select * from table _name; 31] Define SQL Delete statement. Delete is used to delete a row or rows from a table based on the specified condition. The basic syntax is as follows: DELETE FROM table name WHERE 32] What is the Primary key? A Primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. 33] Explain DML and DDL? DML stands for Data Manipulation Language. INSERT, UPDATE and DELETE are DML statements. DDL stands for Data Definition Language. CREATE, ALTER, DROP, RENAME are DDL statements. 34] What do you mean by ROWID? It’s an 18 character long pseudo column attached with each row of a table. 35] What is a composite primary key? Primary key created on more than one column is called composite primary key. 36] Define a temp table? A temp table is a temporary storage structure to store the data temporarily. 37] How can we avoid duplicating records in a query? By using DISTINCT keyword duplicating records in a query can be avoided. 38] What is schema? A schema is a collection of database objects of a User. 39] What is the difference between Local and Global temporary table? If defined in inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the DB but its rows disappear when the connection is closed. 40] What is CTE? A CTE or common table expression is an expression which contains temporary result set which is defined in a SQL statement. 41] What is a unique key? Uniquely identifies a single row in the table. Multiple values allowed per table. Null values allowed. 42] What are the different operators available in SQL? There are three operators available in SQL, namely: Arithmetic Operators Logical Operators Comparison Operators 43] What is the need of MERGE statement? This statement allows conditional update or insertion of data into a table. It performs an UPDATE if a row exists, or an INSERT if the row does not exist. 44] List all the types of user-defined functions? There are three types of user-defined functions, namely: Scalar Functions Inline Table-valued functions Multi-statement valued functions 45] What is a Cursor? A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records. 46] How to store pdf file in SQL Server? Create a column as type ‘blob’ in a table. Read the content of the file and save in ‘blob’ type column in a table. Or Store them in a folder and establish the pointer to link them in the database. 47] What is Union, minus and Interact commands? UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables. MINUS operator is used to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set. 48] What are Tables and Fields? A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records. 49] How the Inner Join in SQL is different from that of Outer Join? An Inner join is the one that is useful for the purpose of returning the rows provided at least two tables are met critically. On the other hand, the outer Join is the one that is useful for returning the value of rows and tables that generally include the records that must be same in all the tables. 50] How can you say that Database testing is different from that of GUI testing? GUI testing is always performed at the front end whereas the Database testing is performed at the back end Structured Query Language largely matters in Database approach where the same doesn’t have any application with the GUI -pranjali sawaikar]]>