#200 A SQL developer is bidding for a new contract

A SQL developer is bidding for a new contract - Computer Science

ChemistryExplain daily providing Q&A content “#200 A SQL developer is bidding for a new contract" in Computer science, Ba computer science, Berkeley computer science, Computer science associate degree jobs

Get the Free Online Chemistry Q&A Questions And Answers with explain. To crack any examinations and Interview tests these Chemistry Questions And Answers are very useful. Here we have uploaded the Free Online Chemistry Questions. Here we are also given the all chemistry topic.

 ChemistryExplain team has covered all Topics related to inorganic, organic, physical chemistry, and others So, Prepare these Chemistry Questions and Answers with Explanation Pdf.

For More Chegg Questions

• Inorganic Chemistry
• Organic Chemistry
• Physical Chemistry

Free Chegg Question

A SQL developer is bidding for a new contract with a prestigious blue-chip organisation. Part of the selection process is a technical interview. Answer the following questions from the interview panel:

a) For each of the following terms, explain what the term stands for, the essence of the functions it provides and a set of example SQL statements (at least TWO for each) that implement these functions:

a) With reference to the above table definitions explain the concept of a Domain. List 2 examples from the above tables of columns that could be defined as a domain

b) Suppose you were required to implement create and populate the tables listed above using an SOL script containing a set of CREATE TABLE and INSERT operations. List the logical sequence the tables should be populated to ensure referential integrity is preserved.

c) TotalotySold is a column that contains derived data Write a SOL SELECT statement that computes a value for this column for a specific product

d) What are the advantages and disadvantages of storing derivable data in a table?

e) Write an SOL UPDATE statement that updates the column Totally Sold given a specific product.

 For More Chemistry Notes and Helpful Content Subscribe Our YouTube Chanel - Chemistry Explain  

Free Chegg Answer

i)DDL(Data Definition Language) : DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

• CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers).
• DROP – is used to delete objects from the database.
• ALTER-is used to alter the structure of the database.
• TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.
• COMMENT –is used to add comments to the data dictionary.
• RENAME –is used to rename an object existing in the database.

Examples of DDL commands:

a)CREATE TABLE Student(
student_id INT,
name VARCHAR(100),
age INT);

b)USE LibraryDB
CREATE TABLE Books
(
Id INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR (50) NOT NULL,
Price INT
)

ALTER TABLE Books

ADD ISBN INT NOT NULL;

ii)DML(Data Manipulation Language) : The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements.

• INSERT – is used to insert data into a table.
• UPDATE – is used to update existing data within a table.
• DELETE – is used to delete records from a database table.

Examples of DML:

a)INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");  

b)UPDATE students    

SET User_Name = 'Sonoo'    

WHERE Student_Id = '3'

iii)DCL(Data Control Language) : DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system.

• GRANT-gives user’s access privileges to database.
• REVOKE-withdraw user’s access privileges given by using the GRANT command.

Examples of DCL commands:

a)GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;  

b)REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;  

Answer : 2)d)

Advantages	Disadvantages
No need to look up source values each time a derivable value is needed	Running data manipulation language (DML) statements against the source data requires recalculation of the derivable data
No need to perform a calculation for every query or report	Data inconsistencies are possible due to data duplication