Basic SQL Flashcards
(196 cards)
1
Q
A
2
Q
What is Data-definition language (DDL)?
A
Provides commands for defining relation schemas, deleting relations, and modifying relation schemas
DDL is essential for setting up the structure of a database.
3
Q
What does Data-manipulation language (DML) do?
A
Provides the ability to query information from the database and to insert, delete, and modify tuples in the database.
DML is crucial for interacting with the data stored in a database.
4
Q
What is the purpose of integrity constraints?
A
Specifies integrity constraints that the data stored in the database must satisfy.
Updates that violate integrity constraints are disallowed.
5
Q
What is a view definition in SQL?
A
Includes commands for defining views.
Views are virtual tables created based on the result-set of a query.
6
Q
What does transaction control in SQL entail?
A
Includes commands for specifying the beginning and ending of transactions.
Transaction control is essential for maintaining data integrity during operations.
7
Q
What is embedded SQL?
A
Defines how SQL statements can be embedded within general-purpose programming languages.
This allows for dynamic interaction with databases from within applications.
8
Q
What does authorization in SQL address?
A
Includes commands for specifying access rights to relations and views.
This ensures that only authorized users can access or manipulate specific data.
9
Q
Define char(n) in SQL.
A
A fixed-length character string with user-specified length n.
The full form, character, can be used instead.
10
Q
What is varchar(n) in SQL?
A
A variable-length character string with user-specified maximum length n.
The full form, character varying, is equivalent.
11
Q
What does int represent in SQL?
A
An integer.
The full form, integer, is equivalent.
12
Q
What is the purpose of the numeric(p, d) type in SQL?
A
A fixed-point number with user-specified precision, consisting of p digits, with d of them to the right of the decimal point.
This allows for precise numerical calculations.
13
Q
What does float(n) represent in SQL?
A
A floating-point number with precision of at least n digits.
This type is used for approximating real numbers.
14
Q
What is the SQL command to create a table?
A
create table R(A1 D1, A2 D2, …, An Dn, <integrity>, <integrity>);</integrity></integrity>
This command establishes a new table structure in the database.
15
Q
What does a primary key in a table ensure?
A
Uniqueness of each record in the table.
A primary key cannot contain NULL values.
16
Q
What is a foreign key in SQL?
A
A field (or collection of fields) in one table that refers to the primary key in another table.
This establishes a relationship between the two tables.
17
Q
How do you insert a new tuple into a table?
A
insert into table_name values (value1, value2, …);
This command adds a new record to the specified table.
18
Q
What SQL command is used to delete all contents of a table?
A
delete from table_name;
This command removes all records but retains the table structure.
19
Q
What does the update command do in SQL?
A
Modifies existing records in a table based on specified criteria.
Example: update table_name set column1 = value1 where condition;
20
Q
What is the function of the ALTER TABLE command?
A
Used to add or drop an attribute to/from a table.
This command allows for modifying the structure of an existing table.
21
Q
What is a typical structure of an SQL query?
A
select A1, A2, … An from r1, r2, … rm where P;
This structure is fundamental for querying data from one or more tables.
22
Q
What is a check constraint in SQL?
A
A rule that limits the values that can be placed in a column.
Example: check (credits > 0); ensures that credits must be greater than 0.
23
Q
What is the typical form of an SQL query?
A
The result of an SQL query is a relation
select A1, A2, … An
from r1, r2, … rm
where P
24
Q
What does the SELECT clause do in SQL?
A
Lists the attributes desired in the result of a query
25
How are SQL names treated in terms of case sensitivity?
SQL names are case insensitive
26
What keyword is used to eliminate duplicates in SQL results?
distinct
27
What does an asterisk (*) in the SELECT clause denote?
All attributes
28
Can the SELECT clause contain arithmetic expressions?
Yes, it can contain arithmetic expressions involving +, -, *, and /
29
What is the result of multiplying the salary attribute by 1.1 in a query?
It returns a relation where the salary is increased by 10%
30
What SQL command would you use to find names of instructors in the Computer Science department?
select name from instructor where dept_name='Comp. Sci.'
31
Fill in the blank: The WHERE clause specifies ______ that the result must satisfy.
conditions
32
What logical connectives can be used to combine comparison results in SQL?
* and
* or
* not
33
How can comparisons be applied in the WHERE clause?
To results of arithmetic expressions
34
What SQL command retrieves names of instructors with salary greater than ₹80000 in Computer Science?
select name from instructor where dept_name='Comp. Sci.' and salary > 80000
35
What does the MOD function do in SQL?
It returns the remainder of a division operation
36
Which SQL command finds instructors whose salary is exactly divisible by 5000?
select ID, Name, Salary from Instructor where mod(Salary,5000)=0
37
Fill in the blank: The result of an SQL query is a ______.
relation
38
What is the purpose of the DISTINCT keyword in SQL?
To eliminate duplicate entries in the result set
39
True or False: SQL allows duplicates in relations.
True
40
What is the output of the command 'select distinct dept_name from instructor'?
Unique department names without duplicates
41
How do you retrieve all attributes from the instructor table?
select * from instructor
42
What is the purpose of using the WHERE clause in SQL?
To filter records based on specified conditions
43
What would the SQL command 'select name from instructor where dept_name != 'Comp. Sci.' and salary > 80000' return?
Names of instructors not in Computer Science with salary greater than ₹80000
44
What does the keyword ALL do in an SQL query?
It specifies that duplicates should not be removed from the result set
45
What is the result of the command 'select * from department'?
It retrieves all records and attributes from the department table
46
What SQL command is used to list all attributes desired in the result of a query?
SELECT clause
47
What does the FROM clause specify in a SQL query?
A list of the relations to be accessed in the evaluation of the query
48
What is the purpose of the WHERE clause in SQL?
A predicate involving attributes of the relation in the FROM clause
49
What is the operational order of SQL clauses?
FROM, then WHERE, and then SELECT
50
What does the FROM clause define by itself in SQL?
A Cartesian product of the relations listed in the clause
51
What does a SELECT * command do in SQL?
Displays the Cartesian product of every tuple in the relations
52
What is the result of using the WHERE clause with matching attributes in SQL?
Displays the valid details of the instructors only once
53
What is a Natural Join in SQL?
Matches tuples with the same values for all common attributes and retains only one copy of each common column
54
What SQL command retrieves the names of instructors along with the course ID of the courses they taught?
SELECT name, courseid FROM instructor, teaches WHERE instructor.id = teaches.id
55
What SQL command uses the natural join to display instructor names and course titles?
SELECT name, title FROM instructor NATURAL JOIN teaches NATURAL JOIN course
56
What are the attributes of the department relation?
deptname, building, budget
57
What are the attributes of the course relation?
courseid, title, deptname, credits
58
What are the attributes of the instructor relation?
ID, name, deptname, salary
59
What are the attributes of the section relation?
courseid, sectionid, sem, year, building, roomnumber, timeslotid
60
What are the attributes of the teaches relation?
ID, courseid, secid, semester, year
61
Fill in the blank: The command 'SELECT * FROM instructor, department;' will display the _______.
Cartesian product of every tuple in the relations
62
True or False: A natural join will work if there is an instructor who teaches a course that belongs to another department.
False
63
How many rows were selected when retrieving the names of instructors and their departments?
12 rows
64
What is the SQL command to retrieve all instructors and their department names?
SELECT name, dept_name FROM instructor, department WHERE instructor.dept_name = department.dept_name
65
What does the 'USING' clause do in a JOIN operation?
Specifies the common attribute used to join the tables
66
What is the SQL command to list the names of instructors along with the titles of courses they teach?
SELECT name, title FROM (instructor NATURAL JOIN teaches) JOIN course USING (courseid)
67
How many rows are selected when displaying the course ID and title?
13 rows
68
What clause is used in SQL to rename relations and attributes?
as clause
## Footnote
The as clause allows for renaming tables or columns in SQL queries.
69
How can you find the names of all instructors who have taught some course?
select name, courseid from instructor i, teaches t where i.id = t.id;
## Footnote
This query joins the instructor and teaches tables based on the instructor ID.
70
What does the SQL operation 'like' do?
Used for string matching with patterns
## Footnote
The 'like' operator is used to search for a specified pattern in a column.
71
What do the percent (%) and underscore (_) characters represent in SQL string operations?
percent (%) matches any substring; underscore (_) matches any single character
## Footnote
These are wildcard characters used for pattern matching in SQL.
72
What is the SQL command to concatenate two strings?
using '||'
## Footnote
The '||' operator is used to concatenate two or more strings in SQL.
73
What SQL function can be used to extract a substring from a string?
substr(name, start_position, length)
## Footnote
This function extracts a portion of the string starting from a specified position.
74
What does the SQL command 'order by' do?
Sorts the result set in a specified order
## Footnote
You can specify 'asc' for ascending or 'desc' for descending order.
75
What SQL predicate is used to filter results within a range?
between - and
## Footnote
This predicate allows you to specify a range for numeric or date values.
76
What are aggregate functions in SQL?
Functions that return a single value from a set of values
## Footnote
Examples include avg, min, max, sum, and count.
77
Which SQL function would you use to find the maximum value in a column?
max()
## Footnote
The max function returns the highest value from a specified column.
78
What is the SQL command to count the number of entries in a column?
count()
## Footnote
The count function counts the number of rows that match the specified criteria.
79
Fill in the blank: The _ character in SQL matches _______.
any single character
80
True or False: The 'like' operator can only be used with numeric data types.
False
## Footnote
The 'like' operator is primarily used with string data types.
81
What SQL function is used to convert strings to uppercase?
upper(s)
## Footnote
The upper function converts all characters in a string to uppercase.
82
What SQL function would you use to remove trailing spaces from a string?
trim(s)
## Footnote
The trim function removes spaces from the beginning and end of a string.
83
What does the SQL query 'select * from instructor order by name desc;' do?
Retrieves all records from the instructor table sorted by name in descending order
## Footnote
'desc' specifies the order of sorting.
84
What is the purpose of the 'distinct' keyword in SQL?
Eliminates duplicate values from the result set
## Footnote
The distinct keyword ensures that the results returned are unique.
85
How can you find instructors with a salary greater than a specific amount?
select name from instructor where salary > amount;
## Footnote
Replace 'amount' with the specific salary figure.
86
What is the minimum salary from the instructor table?
40000
## Footnote
This value represents the lowest salary recorded among the instructors.
87
What is the total count of salaries in the instructor table?
12
## Footnote
This indicates the total number of instructors listed.
88
What is the average salary of instructors?
74833.3333
## Footnote
This is calculated by summing all salaries and dividing by the number of instructors.
89
What SQL command retrieves all records from the instructor table?
select * from instructor
## Footnote
This command fetches all columns for each instructor.
90
Which department has the highest average salary according to the data?
Physics
## Footnote
Physics has an average salary of 91000.
91
What is the average salary for the Computer Science department?
77333.3333
## Footnote
This is based on the average of all instructors in that department.
92
What SQL command is used to find the average salary of instructors in each department?
select dept_name, avg(Salary) from instructor group by dept_name
## Footnote
This groups the results by department and calculates the average salary.
93
How many instructors teach a course in the Fall 2009 semester in Computer Science?
2
## Footnote
This indicates the number of instructors specifically teaching in that semester and department.
94
What is the purpose of the HAVING clause in SQL?
To apply conditions to groups rather than individual rows
## Footnote
This allows for filtering based on aggregate calculations.
95
Fill in the blank: The _______ clause must appear in the select clause for any attribute not present in the group by clause.
aggregate function
## Footnote
This is crucial for ensuring the query's correctness.
96
True or False: All aggregate functions ignore null values in their calculations.
False
## Footnote
Only the count(*) function includes null values; others do not.
97
What SQL command finds departments where the average salary is more than ₹42,000?
select dept_name, avg(salary) from instructor group by dept_name having avg(salary) > 42000
## Footnote
This filters departments based on their average salary.
98
What is the order of execution for SQL queries?
from, where, group by, having, order by, select
## Footnote
This sequence is essential for understanding how SQL processes commands.
99
Which SQL command retrieves all courses offered in the Fall 2009 semester?
select courseid from section where semester = 'Fall' and year = 2009
## Footnote
This command filters courses based on semester and year.
100
What is the result of the UNION operation in SQL?
Combines results from two or more SELECT statements
## Footnote
This operation eliminates duplicate records from the result set.
101
Fill in the blank: Aggregate functions in SQL can be used to summarize data, such as finding the _______ of a column.
average
## Footnote
Other common aggregate functions include sum, count, and min.
102
What SQL command can be used to find the total number of instructors who teach a course in the Spring 2020 semester?
select count(distinct id) from teaches where semester = 'Spring' and year = 2020
## Footnote
This counts unique instructor IDs teaching during that semester.
103
What SQL command is used to select course IDs for Fall 2009?
select course_id from section where semester = 'Fall' and year = 2009
104
What SQL command is used to select course IDs for Spring 2009?
select course_id from section where semester = 'Spring' and year = 2009
105
What does the union operation do in SQL?
The union operation automatically eliminates duplicates
106
What SQL command is used to find courses offered in both Fall 2009 and Spring 2010?
select course_id from section where semester = 'Spring' and year = 2010 intersect select course_id from section where semester = 'Fall' and year = 2009
107
What SQL command is used to find courses offered in Spring 2010 but not in Fall 2009?
select course_id from section where semester = 'Spring' and year = 2010 minus select course_id from section where semester = 'Fall' and year = 2009
108
What does NULL signify in SQL?
An unknown value or that a value does not exist
109
What is the result of 'is null' in SQL?
True if the value is null
110
What is the result of 'is not null' in SQL?
True if the value is not null
111
What SQL command is used to determine eligibility based on salary and bonus?
SELECT name, CASE WHEN salary >= 50000 AND bonus >= 5000 THEN 'Eligible' ELSE 'Not Eligible' END AS eligibility_status FROM employees
112
What is a subquery in SQL?
A select-from-where expression that is nested within another query
113
What is set membership in SQL?
Checked using 'in' and 'not in' constructs
114
Fill in the blank: The __________ operator eliminates duplicates.
union
115
True or False: The 'not in' operator tests for the absence of set membership.
True
116
What SQL command is used to find students who have not taken any course yet?
select name from student where id not in (select id from takes)
117
What are the attributes of the 'department' relation?
dept_name, building, budget
118
What are the attributes of the 'course' relation?
course_id, title, dept_name, credits
119
What are the attributes of the 'instructor' relation?
ID, name, dept_name, salary
120
What are the attributes of the 'section' relation?
course_id, sec_id, semester, year, building, room_number, time_slot_id
121
What are the attributes of the 'student' relation?
ID, name, dept_name, tot_cred
122
What are the attributes of the 'takes' relation?
ID, course_id, sec_id, semester, year, grade
123
What SQL command is used to find instructors whose names are neither 'Mozart' nor 'Einstein'?
SELECT * FROM instructor WHERE name NOT IN ('Mozart', 'Einstein')
124
What SQL command is used to find the total number of distinct students who have taken course sections taught by a specific instructor?
select count(distinct id) as total_students from takes where (course_id, sec_id, semester, year) IN (select course_id, sec_id, semester, year from teaches where id = 10101)
125
What SQL function is used to count distinct students?
count(distinct id)
## Footnote
This function counts the number of unique student IDs in the 'takes' table.
126
What is the purpose of the nested subquery in the SQL statement?
To filter records based on a condition from another query
## Footnote
The outer query selects students based on course and section taught by a specific instructor.
127
What SQL clause is used to compare a value against a set of values?
IN
## Footnote
The IN clause checks if a value matches any value in a list or subquery.
128
What does the term 'total_students' represent in the SQL query?
The total number of distinct students enrolled in specific courses
## Footnote
This is calculated using the count function.
129
Which SQL command retrieves all records from the 'takes' table?
select * from takes
## Footnote
This command selects all columns and rows from the 'takes' table.
130
What is the result of the SQL query that counts distinct students for a specific instructor?
6
## Footnote
This indicates there are six unique students enrolled in the courses taught by the instructor with id 10101.
131
How do you find instructors with a salary greater than at least one instructor in the Finance department?
Using '>' comparison with a nested subquery
## Footnote
This retrieves names of instructors whose salaries exceed any salary in the Finance department.
132
What does the 'some' clause signify in SQL?
It checks if the condition is true for at least one member of a set
## Footnote
For example, salary > some (subquery) retrieves instructors with salaries greater than at least one in the specified set.
133
What SQL operator is used to find instructors with salaries less than at least one instructor in Finance?
< some
## Footnote
This operator checks if the salary is less than any salary from the Finance department.
134
What is the output of the SQL query that selects names of instructors with a salary not equal to any in Finance?
Srinivasan, Mozart, El Said, Gold, Katz, Califieri, Crick, Brandt, Kim
## Footnote
These instructors have salaries that do not match any of those in the Finance department.
135
True or False: The SQL command 'SELECT name FROM instructor WHERE salary > SOME (SELECT salary FROM instructor WHERE dept_name = 'Finance');' retrieves all instructors with a salary higher than all Finance instructors.
False
## Footnote
The SOME clause retrieves instructors with a salary higher than at least one Finance instructor, not all.
136
Fill in the blank: The SQL command 'SELECT * FROM instructor WHERE dept_name = ______;' retrieves instructors from the Finance department.
Finance
## Footnote
This command filters instructors based on their department name.
137
What will the SQL command 'SELECT name FROM instructor WHERE salary <> SOME (SELECT salary FROM instructor WHERE dept_name = 'Finance');' return?
Instructors with salaries not equal to any Finance instructor's salary
## Footnote
This retrieves names of instructors whose salaries differ from those in the Finance department.
138
What is the SQL command to retrieve all records from the instructor table?
SELECT * FROM instructor
139
What does the SQL command 'WHERE dept_name = 'Finance'' do?
Filters instructors by the Finance department
140
What is the salary of instructor Wu?
90000
141
What SQL command would you use to find instructors with a salary not equal to any salary in the Biology department?
SELECT name FROM instructor WHERE salary <> ALL (SELECT salary FROM instructor WHERE dept_name = 'Biology')
142
How many rows are selected when querying the instructor table?
11 rows selected
143
What SQL operator checks if a value exists in a subquery?
EXISTS
144
True or False: The EXISTS operator returns true if the subquery is empty.
False
145
What is a correlated subquery?
A subquery that uses a correlation name from an outer query
146
What does the NOT EXISTS operator do?
Returns true if the subquery is empty
147
Fill in the blank: The SQL command 'SELECT name FROM instructor WHERE salary > _______' selects instructors with a salary greater than a specified value.
[specified value]
148
What SQL command would you use to find all instructors who have a salary greater than each instructor in the Finance department?
SELECT name FROM instructor WHERE salary > ALL (SELECT salary FROM instructor WHERE dept_name = 'Finance')
149
In SQL, what does the operator '<> ALL' signify?
Not equal to all values in a subquery
150
What is the result type of the EXISTS operator?
Boolean value TRUE or FALSE
151
What SQL command checks for instructors who do not teach any course?
SELECT * FROM instructor WHERE NOT EXISTS (SELECT * FROM teaches WHERE instructor.ID = teaches.ID)
152
What does the SQL command 'SELECT course_id FROM section S WHERE semester = 'Fall' AND year = 2009 AND EXISTS (...)' do?
Finds courses offered in Fall 2009 that are also offered in Spring 2010
153
What is the purpose of the SQL command 'SELECT * FROM customers WHERE NOT EXISTS (...)'?
Returns all records from customers where there are no matching records in orderdetails
154
What SQL command would you use to find all students who have taken all courses offered in the Finance department?
SELECT id, name FROM student s WHERE NOT EXISTS ((SELECT course_id FROM course c WHERE c.dept_name = 'Finance') EXCEPT (SELECT course_id FROM takes t WHERE t.ID = s.ID))
155
What does the 'IN' operator do in SQL?
Returns true if the value is present in the result of a subquery
156
What is the significance of the 'EXISTS' operator in SQL?
It terminates the processing of the subquery once the first row is returned
157
What SQL command retrieves the course_id from section S for Fall 2009?
SELECT course_id FROM section S WHERE semester = 'Fall' AND year = 2009
158
Fill in the blank: The SQL command 'SELECT * FROM tablename WHERE id IN _______' checks for presence of id in a subquery result.
[subquery]
159
What SQL command can be used to find the departments with the highest average salary?
SELECT dept_name FROM instructor GROUP BY dept_name HAVING AVG(salary) = (SELECT MAX(avg_salary) FROM (SELECT AVG(salary) AS avg_salary FROM instructor GROUP BY dept_name))
160
What SQL command is used to find those instructors who do not teach any course?
SELECT id, name FROM instructor WHERE NOT EXISTS (SELECT course_id FROM teaches WHERE instructor.ID = teaches.ID)
161
How can you find all students who have taken all courses offered in the Finance department?
SELECT id, name FROM student s WHERE NOT EXISTS ((SELECT course_id FROM course WHERE dept_name = 'Finance') MINUS (SELECT course_id FROM takes t WHERE s.id = t.id))
162
What is the structure of the 'takes' relation?
takes(ID, course_id, sec_id, semester, year, grade)
163
What SQL query retrieves the average instructor’s salaries of departments where the average salary is greater than ₹42000?
SELECT dept_name, avg_salary FROM (SELECT dept_name, AVG(salary) AS avg_salary FROM instructor GROUP BY dept_name) dept_avg WHERE avg_salary > 42000
164
What is a scalar subquery?
A scalar subquery is one that returns only one tuple containing a single attribute.
165
True or False: Scalar subqueries can only occur in the SELECT clause.
False
166
List the attributes of the 'department' relation.
* dept_name
* building
* budget
167
Fill in the blank: The _______ clause defines a temporary relation used in the immediately following query.
WITH
168
What is the purpose of the 'WITH' clause in SQL?
To define a temporary relation that can be used in subsequent queries.
169
What is the structure of the 'instructor' relation?
instructor(ID, name, dept_name, salary)
170
What SQL query would you use to find the maximum salary across all departments?
SELECT MAX(salary) FROM instructor
171
What SQL command finds those departments with the maximum budget using the WITH clause?
WITH maxbudget(maxb) AS (SELECT MAX(budget) AS maxb FROM department) SELECT dept_name FROM department, maxbudget WHERE budget > maxbudget.maxb/2
172
What is the output of the SQL query that selects the department names and average salaries from departments with an average salary greater than ₹42000?
Comp. Sci., Finance, Physics, Biology, Elec. Eng.
173
List the attributes of the 'course' relation.
* course_id
* title
* dept_name
* credits
174
What is the result of the SQL query that counts the number of instructors in each department?
List of departments along with the corresponding number of instructors.
175
True or False: Nested subqueries in the FROM clause can use correlation variables from other relations in the FROM clause.
False
176
What SQL query retrieves all departments where the total salary is greater than the average of total salaries across all departments?
WITH depttotal(deptname, val) AS (SELECT dept_name, SUM(salary) FROM instructor GROUP BY dept_name), depttotavg(val) AS (SELECT AVG(val) FROM depttotal) SELECT deptname, depttotal.val FROM depttotal, depttotavg WHERE depttotal.val >= depttotavg.val
177
What is the structure of the 'student' relation?
student(ID, name, dept_name, tot_cred)
178
Fill in the blank: Subqueries in the _______ clause cannot use correlation variables from other relations.
FROM
179
What SQL command is used to count the number of instructors in a department?
SELECT dept_name, (SELECT COUNT(*) FROM instructor WHERE department.dept_name = instructor.dept_name) AS numinstructor
180
Where can scalar subqueries occur in SQL?
SELECT, WHERE, and HAVING clauses
181
What happens if a scalar subquery returns more than one tuple?
A runtime error occurs
182
What is the technical type of a scalar subquery result?
It is still a relation, even if it contains a single tuple
183
What is a key consideration before performing any deletion in a database?
Performing all the tests before performing any deletion is important
184
Write the SQL command to delete all instructors associated with the Finance department.
DELETE FROM instructor WHERE dept_name = (SELECT dept_name FROM department WHERE building='AB5')
185
What is the purpose of the INSERT command in the context of database modification?
To add new records into a table
186
What is the SQL command to insert students from the Physics department who have more than 50 credit hours as instructors?
INSERT INTO instructor (id, name, dept_name, salary) SELECT id, name, 'Physics', 18000 FROM student WHERE dept_name = 'Physics' AND tot_cred > 50
187
How do you update the total credits of each student based on successfully completed courses?
UPDATE student S SET totcred = (SELECT SUM(credits) FROM takes NATURAL JOIN course WHERE S.ID = takes.ID AND takes.grade <> 'F' AND takes.grade IS NOT NULL)
188
What SQL command would you use to find the titles of courses in the Comp. Sci. department that have 3 credits?
SELECT title FROM course WHERE dept_name = 'Comp. Sci.' AND credits = 3
189
What is the SQL command to find the highest salary of any instructor?
SELECT MAX(salary) FROM instructor
190
True or False: You can find all instructors earning the highest salary using a single SQL command.
True
191
What SQL command would you use to increase the salary of each instructor in the Comp. Sci. department by 10%?
UPDATE instructor SET salary = salary * 1.10 WHERE dept_name = 'Comp. Sci.'
192
Fill in the blank: To delete all courses that have never been offered, you would use the command: DELETE FROM course WHERE _______.
course_id NOT IN (SELECT course_id FROM section)
193
What is a condition for inserting students with total credits greater than 100 as instructors?
They must be inserted in the same department with a salary of ₹10000
194
How do you ensure there are no duplicate names when finding students who have taken at least one Comp. Sci. course?
Use DISTINCT in the SELECT statement
195
What SQL command would you use to find the maximum salary of instructors in each department?
SELECT dept_name, MAX(salary) FROM instructor GROUP BY dept_name
196
What is the goal of finding the lowest per-department maximum salary across all departments?
To determine the minimum of the maximum salaries computed from the preceding query
