# Recent questions tagged sql

Is the JOIN operations(natural , left , right) in relational algebra and SQL same or is there any difference based on the condition? Please provide reference.
1 vote
What is degree of Self Referential Relationship? Unary or Binary?
Can Somebody know how to design ER Diagram from this ?
1 vote
The relation scheme given below is used to store information about the employees of a company, where $\textsf{empId}$ is the key and $\textsf{deptId}$ indicates the department to which the employee is assigned. Each employee is assigned to exactly ... than the average salary of employees in the department employees in the company female employees in the department female employees in the company
A relation $r(A, B)$ in a relational database has $1200$ tuples. The attribute $A$ has integer values ranging from $6$ to $20$, and the attribute $B$ has integer values ranging from $1$ to $20$. Assume that the attributes $A$ and $B$ are independently distributed. The estimated number of tuples in the output of $\sigma _{(A>10)\vee(B=18)}(r)$ is ____________.
A:Result of Sids of suppliers who supply some red or green part. B:Result of Sids of suppliers who do not supply any red or green part. C:Result of Sids of suppliers who supply every red or green part. D:Result of Sids of suppliers who do not supply some red or green part.
If we define a UNIQUENESS Constarint on a column of table then under that column can we have multiple NULL entries.??
question no – 15
NOTE: * denotes primary key; f denotes foreign key Que: Consider the above schema and give relational algebra query for the following 1. Find no. of students who have taken the courses taught by 'Joe' and not registered in any other courses, also they should have ... taking place in PLACEID 100. 5. Find the details of student who has score GRADEPOINT A in all the course he/she is registered.
1 vote
The relation book (title,price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list? select title from book as B where (select count(*) from book as T where T.price ... four most expensive books Title of the fifth most inexpensive book Title of the fifth most expensive book Titles of the five most expensive books
Which of the following statements are TRUE about an SQL query? P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause Q : An SQL query can contain a HAVING clause only if it has a GROUP BY clause R : All attributes used in the GROUP BY clause ... S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause P and R P and S Q and R Q and S
Consider the following relational schema: $\text{Suppliers}(\underline{\text{sid:integer}},\text{ sname:string, city:string, street:string})$ $\text{Parts}(\underline{\text{pid:integer}}, \text{ pname:string, color:string})$ ... the names of all suppliers who have supplied only non-blue part. Find the names of all suppliers who have not supplied only blue parts.
1 vote
Consider the table employee(empId, name, department, salary) and the two queries $Q_1, \, Q_2$ below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements ... $Q_2$ is the correct query Both $Q_1$ and $Q_2$ produce the same answer Neither $Q_1$ nor $Q_2$ is the correct query
Consider the following relational schema: $\text{Suppliers}(\underline{\text{sid:integer}},\text{ sname:string, city:string, street:string})$ $\text{Parts}(\underline{\text{pid:integer}}, \text{ pname:string, color:string})$ ... is in $3NF$ but not in $\text{BCNF}$ The schema is in $2NF$ but not in $3NF$ The schema is not in $2NF$
Consider the relation account (customer, balance) where the customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. Ties are not broke but ranks are skipped: if ... order assigning ranks using ODBC. Which two of the above statements are correct? 2 and 5 1 and 3 1 and 4 3 and 5
Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available in the database. ... using primary key, then $n$ lies in the range: $36 - 40$ $44 - 48$ $60 - 64$ $100 - 104$
Given the following statements: S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL. S2: Given the table $R(a,b,c)$ where $a$ and $b$ together form the primary key, the following is a valid table definition. CREATE TABLE S ( a INTEGER, d ... is CORRECT? S1 is TRUE and S2 is FALSE Both S1 and S2 are TRUE S1 is FALSE and S2 is TRUE Both S1 and S2 are FALSE
Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the ... for which Query3 returns strictly fewer rows than Query2 There exist databases for which Query4 will encounter an integrity violation at runtime
SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below: select * from R where a in (select S.a from S) select R.* from R, S where R.a ... R,(select distinct a from S) as S1 where R.a=S1.a select R.* from R,S where R.a=S.a and is unique R
In SQL, relations can contain null values, and comparisons with null values are treated as unknown. Suppose all comparisons with a null value are treated as false. Which of the following pairs is not equivalent? $x = 5 \quad not (not (x = 5))$ $x = 5 \quad x > 4$ and $x < 6,$ where $x$ is an integer $x ≠ 5 \quad not (x = 5)$ none of the above
A relational database contains two tables Student and Performance as shown below: ... Marks) FROM Student S, Performance P WHERE P.Marks >84 GROUP BY S.Student_name; The number of rows returned by the above SQL query is ________
Consider the following database table named $\text{top_scorer}$ ... > ANY (SELECT tc.goals FROM top_scorer AS tc WHERE tc.country='Germany') The number of tuples returned by the above SQL query is ______
Given relations r(w, x) and s(y, z) the result of select distinct w, x from r, s is guaranteed to be same as r, provided. r has no duplicates and s is non-empty r and s have no duplicates s has no duplicates and r is non-empty r and s have the same number of tuples
1 vote
Which of the following is/are correct? An SQL query automatically eliminates duplicates An SQL query will not work if there are no indexes on the relations SQL permits attribute names to be repeated in the same relation None of the above
1 vote
Consider the following relation: ... Student S, Performance P WHERE S.Roll_No= P.Roll_No GROUP BY S.STUDENT_Name The numbers of rows that will be returned by the SQL query is_________________.
1 vote
Consider the following relational schema. Students(rollno: integer, sname: string) Courses(courseno: integer, cname: string) Registration(rollno: integer, courseno: integer, percent: real) Which of the following queries are equivalent to this query in English? Find the distinct names of all students who ... I, II, III and IV I, II and III only I, II and IV only II, III and IV only
Student (school-id, sch-roll-no, sname, saddress) School (school-id, sch-name, sch-address, sch-phone) Enrolment(school-id sch-roll-no, erollno, examname) ExamResult(erollno, examname, marks) What does the following SQL query output? SELECT sch-name, COUNT (*) FROM ... , the name of the school and the number of its students scoring 100 in at least one exam nothing; the query has a syntax error
Consider a database that has the relation schema EMP (EmpId, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below: ... ) IN (SELECT DeptName, COUNT(EmpId) AS EC(DeptName, Num) FROM EMP GROUP BY DeptName) The output of executing the SQL query is _____________ .
1 vote
Consider the following relational schema: employee (empId,empName,empDept) customer (custId,custName,salesRepId,rating) salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the ... of their customers having a 'GOOD' rating. Names of all the employees with all their customers having a 'GOOD' rating.
Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Assume that amounts 6000, 7000, 8000 ... , Plan 1 executes faster than Plan 2 for all databases For x = 9000, Plan I executes slower than Plan 2 for all databases
Given the following schema: employees(emp-id, first-name, last-name, hire-date, dept-id, salary) departments(dept-id, dept-name, manager-id, location-id) You want to display the last names and hire dates of all latest hires in their respective departments in the ... because of pairwise comparison. It generates an error because of the GROUP BY clause cannot be used with table joins in a sub-query.
In an inventory management system implemented at a trading corporation, there are several tables designed to hold all the information. Amongst these, the following two tables hold information on which items are supplied by which suppliers, and which warehouse keeps which items ... all suppliers who do not supply any item supply exactly one item supply one or more items supply two or more items
1 vote
Consider the following database table named water_schemes: ... district_name with total_avg (capacity) as select avg (capacity) from total select name from total, total_avg where total.capacity ≥ total_avg.capacity
1 vote
Consider the set of relations shown below and the SQL query that follows. Students: (Roll_number, Name, Date_of_birth) Courses: (Course_number, Course_name, Instructor) Grades: (Roll_number, Course_number, Grade) Select distinct Name from Students, Courses, Grades where Students.Roll_number= ... of students who have got an A grade in at least one of the courses taught by Korth None of the above
Consider the following relations $A, B$ and $C:$ ... following SQL query contain? SELECT A.Id FROM A WHERE A.Age > ALL (SELECT B.Age FROM B WHERE B.Name = Arun') $4$ $3$ $0$ $1$
A table 'student' with schema (roll, name, hostel, marks), and another table 'hobby' with schema (roll, hobbyname) contains records as shown below: ... $S'$ is $6$ $4$ $2$ $0$
Consider the following relation Cinema($\textit{theater, address, capacity}$) Which of the following options will be needed at the end of the SQL query SELECT P1.address FROM Cinema P1 such that it always finds the addresses of theaters with maximum capacity? WHERE P1.capacity ... > All (select max(P2.capacity) from Cinema P2) WHERE P1.capacity > Any (select max(P2.capacity) from Cinema P2)
Consider a relation geq which represents "greater than or equal to", that is, $(x,y) \in$ geq only if $y \geq x$. create table geq ( ib integer not null, ub integer not null, primary key ib, foreign key (ub) references geq on delete cascade ); Which of the following is possible ... deleted A tuple (z,w) with z > x is deleted A tuple (z,w) with w < x is deleted The deletion of (x,y) is prohibited
Consider a database table T containing two columns $\text{X}$ and $\text{Y}$ each of type $\text{integer}$. After the creation of the table, one record $\text{(X=1, Y=1)}$ is inserted in the table. Let $\text{MX}$ and $\text{MY}$ denote the respective ... What will be the output of the following SQL query after the steps mentioned above are carried out? SELECT Y FROM T WHERE X=7; 127 255 129 257