SQL PLSQL Interview Questions
Questions Answers Views Company eMail

what is query cache in mysql? : Sql dba

811

what is self-join? : Sql dba

795

what is row? : Sql dba

978

what are the nonstandard string types? : Sql dba

856

what is self join and what is the requirement of self join? : Sql dba

897

what is self join and why is it required? : Sql dba

1 1245

what is 'trigger' in sql? : Sql dba

778

what is log shipping? : Sql dba

887

what is index? : Sql dba

784

how to use 'mysql' to run sql statements? : Sql dba

791

how to implement one-to-one, one-to-many and many-to-many relationships while designing tables? : Sql dba

772

what are properties of a transaction? : Sql dba

806

where are cookies actually stored on the hard disk? : Sql dba

833

what is a trigger in mysql? : Sql dba

833

what's the difference between a primary key and a unique key? : Sql dba

755


Post New SQL PLSQL Questions

Un-Answered Questions { SQL PLSQL }

Hi, I am new in oracle(SQL), could anyone help me in writing a correct SQL. Below is the table structure. Table: Subsc Fields: 1. Sub_no (this field will hold values of subscriber nos, for e.g. S111111, S222222, S333333, S444444, etc.) 2. s_status (this field will hold values for different status of subscriber, for e.g. 'A', 'S', 'C', etc.) 3. cus_id (this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) Table: Bill Fields: 1. Bill_no this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) 2. b_status = (this field will hold values for different status of bill for e.g. 'O', 'C', 'S', etc.) Note: 1. The Sub_no is a Primary key of Subsc table. 2. The cus_id is a foreign in Subsc table (referred from Bill_no field of Bill table) 3. The Bill_no field is the Primary key of Bill table. Query A --> I wrote a query to select cus_id/Bill_no which is in status open (b_status = 'O') and having more than two active subscriber (i.e. S_status = 'A') in it ( i.e. more the two subscribers in same bill). select s.cus_id from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id having count(sub_no) = 2 Problem : The above query will give the cus_id (or rather bill_no) which are in open status (b_status ='O) and which are having TWO ACTIVE Subscribers (s_status ='A') in it. However, this query will also lists the cus_id/bill_no which are having more than TWO subscribers in it (but only two subscriber will be in Active status (s_status = 'A') and the others will be in s_status = 'C' or s_status = 'S'. Help needed: I want to write a query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it. B--> If I include the sub_no in the above query then NO row are returned. select s.cus_id, s.sub_no from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id, s.sub_no having count(sub_no) = 2 Help needed: I want to modify the above query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it ALONG with the sub_no. Thanks a lot in advance. Regards, Nitin

2032


What is sql clause?

791


What is the use of index in hive?

772


What is the difference between row level and statement level trigger?

776


Can I call a procedure inside a function?

782


How do I delete a trigger?

756


How to Execute a Package in PL/SQL.?

859


How to check if a column is nullable before setting to nullable?

834


define data blocks ? : Sql dba

769


What is exception? What are the types of exceptions?

835


How do I write a cron which will run a sql query and mail the results to agroup?

761


What is a sql instance vs database?

813


Is sql free?

707


What are tuples in sql?

828


Why we use sql profiler?

761