Infotrack Systems SQL Developer Interview Questions and Answers

Updating customer table

• Use UPDATE statement with SET clause to update table

• Specify the column to be updated and the new value

• Use WHERE clause to specify the condition for updating specific rows

To delete customer table, use DROP TABLE statement.

• Use DROP TABLE statement followed by table name to delete the table.

• Make sure to take backup of the table before deleting it.

• Ensure that there are no dependencies on the table before deleting it.

Creating customer table in SQL

• Define the table structure with appropriate data types for each column

• Include columns for customer ID, name, address, email, phone number, etc.

• Set appropriate constraints such as primary key, unique, not null, etc.

• Consider adding additional columns for date of birth, gender, etc. if required

• Create indexes on frequently queried columns for better performance

Triggers are special types of stored procedures that are automatically executed in response to certain events.

• Triggers are used to enforce business rules or to perform complex calculations that involve multiple tables.

• They can be used to audit changes to data, or to replicate data across multiple tables.

• Triggers can be defined to execute before or after an INSERT, UPDATE, or DELETE statement.

• They can also be defined to...read more

Cursors are used to retrieve data from a result set one row at a time.

• Cursors are used when we need to perform operations on individual rows of a result set.

• They are declared, opened, fetched, and closed.

• They can be used to update or delete data in a table.

• They can be slow and memory-intensive, so should be used sparingly.

• Example: DECLARE cursor_name CURSOR FOR SELECT * FROM table_name;

• Example: OPEN cursor_name; FETCH ...read more

Joins are used to combine data from two or more tables based on a related column.

• Types of joins: inner, left, right, full outer

• Syntax: SELECT * FROM table1 JOIN table2 ON table1.column = table2.column

• Can also use aliases for table names and columns

• Joins can be nested or chained together

Indexes are used to improve the performance of SQL queries by reducing the time taken to retrieve data.

• Indexes are created on one or more columns of a table.

• They can be clustered or non-clustered.

• Clustered indexes determine the physical order of data in a table.

• Non-clustered indexes create a separate structure to hold the indexed data.

• Indexes should be used judiciously as they can slow down data modification operations...read more

Subsets of SQL are different types of SQL languages used for specific purposes.

• Data Definition Language (DDL) - used to define database schema

• Data Manipulation Language (DML) - used to manipulate data in database

• Data Control Language (DCL) - used to control access to database

• Transaction Control Language (TCL) - used to manage transactions

• Examples: CREATE, SELECT, INSERT, UPDATE, DELETE, GRANT, REVOKE, COMMIT, ROLLBACK

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

• Normalization involves breaking down a table into smaller tables and defining relationships between them.

• There are different levels of normalization, with each level having specific rules to follow.

• Normalization helps to prevent data inconsistencies and anomalies.

• Example: A customer table can be normalized into...read more

Types of joins in SQL are Inner Join, Left Join, Right Join, Full Outer Join, Cross Join.

• Inner Join returns only the matching rows from both tables.

• Left Join returns all the rows from the left table and matching rows from the right table.

• Right Join returns all the rows from the right table and matching rows from the left table.

• Full Outer Join returns all the rows from both tables.

• Cross Join returns the Cartesian produc...read more

To update customer table, use UPDATE statement with SET clause and WHERE clause.

• Use UPDATE statement to modify data in customer table

• Use SET clause to specify the columns to be updated and their new values

• Use WHERE clause to specify the rows to be updated based on a condition

• Example: UPDATE customer SET name='John Doe' WHERE id=1

To delete customer table in SQL, use the DROP TABLE command.

• Use the DROP TABLE command followed by the table name.

• Make sure to backup the data before deleting the table.

• Ensure that there are no dependencies on the table before deleting it.

To create a customer table, define columns for customer information and set primary key.

• Define columns for customer information such as name, address, phone number, email, etc.

• Set primary key for unique identification of each customer.

• Consider adding constraints for data validation and normalization.

• Example: CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(50), address VARCHAR(100), phone VARCHAR(20), email VAR...read more

Triggers are special types of stored procedures that are automatically executed in response to certain events.

• Triggers are used to enforce business rules or data integrity.

• They can be used to audit changes to data.

• Triggers can be defined to execute before or after a data modification statement.

• They can be defined at the table or schema level.

• Examples include: enforcing referential integrity, logging changes to a table,...read more

Cursors are used to retrieve data row by row from a result set.

• Cursors are used when we need to perform operations on individual rows.

• They are declared, opened, fetched, and closed.

• They can be used to iterate through a result set.

• They can be static, dynamic, or forward-only.

• Example: DECLARE cursor_name CURSOR FOR SELECT * FROM table_name;

• Example: OPEN cursor_name; FETCH NEXT FROM cursor_name INTO @variable;

• Example: CLO...read more

Joins are used to combine data from two or more tables based on a related column.

• Types of joins: inner, left, right, full outer

• Syntax: SELECT * FROM table1 JOIN table2 ON table1.column = table2.column

• Can also use aliases for table names and columns

• Joins can be nested or chained together

Indexes are used to improve the performance of SQL queries by allowing faster data retrieval.

• Indexes are created on one or more columns of a table.

• They can be clustered or non-clustered.

• Clustered indexes determine the physical order of data in a table.

• Non-clustered indexes create a separate structure to hold the index data.

• Indexes should be used judiciously as they can slow down data modification operations.

Subsets of SQL are subsets of SQL language that are used for specific purposes.

• Common subsets include DDL, DML, DCL, and TCL

• DDL (Data Definition Language) is used to define database schema

• DML (Data Manipulation Language) is used to manipulate data in the database

• DCL (Data Control Language) is used to control access to the database

• TCL (Transaction Control Language) is used to manage transactions

• Other subsets include DQL...read more

Normalization is the process of organizing data in a database to reduce redundancy and dependency.

• Normalization is divided into several normal forms (1NF, 2NF, 3NF, etc.)

• Each normal form has a set of rules that must be followed to achieve it

• Normalization helps to improve data consistency, reduce data redundancy, and improve database performance

• Example: A customer table should not contain order details, instead, it shou...read more

Types of joins in SQL are Inner Join, Left Join, Right Join, and Full Outer Join.

• Inner Join returns only the matching rows from both tables.

• Left Join returns all the rows from the left table and matching rows from the right table.

• Right Join returns all the rows from the right table and matching rows from the left table.

• Full Outer Join returns all the rows from both tables.

• Joining tables using a common column is the mos...read more