|
The SQL Server books online have lots of trigger examples, a trigger is code that runs when a table is updated or inserted, in that code, you can examine the values that were changed or added, and modify your actions according to those values.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
|
I would seem that what you actually want to do is use the database to control a schedule: by setting the start date and time in some field in a table you want to be notified when that entry equals the current system time.
I don't know of any way to do this purely in the database without any external program help. You would need to poll the database and do the comparison yourself programmatically...
|
|
|
|
|
i want to create a trigger.in that trigger when modifying a value from a categorytable i want to check whether any another table in the database containing category id with that value .if it is there i want to display one message.How can i do that?.............
|
|
|
|
|
You can query sysobjects for tables in your database and syscolumns for their respective columns (based on ID).Run these queries on your database
<br />
select * from sysobjects<br />
select * from syscolumns<br />
|
|
|
|
|
YESTERDAY I POSTED SOME QUERIES I NEED THEM NOW BUT THEY HAVE BEEN MOVED
WOTS THE REASON?
I NEEDED ALTERNATIVES FOR THOSE QUERIES
|
|
|
|
|
maybe its your altitude...
|
|
|
|
|
You posted the same question 6 times in a day. They got marked as spam, probably because you kept asking them over and over.
If you follow the forum rules, and abide by simple netiquette, your posts won't be marked down.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
hello,
iam working on an application which has a database, i wanna deploy my project and create an installer to it but i have a problem with the database, how can i install database with my project,
and if i create a demo version of the project how can i make the application work without installing MS SQL Server, can i do that???
in other words should MS SQL Server 2000 be installed on the machine to make the application running ? or can i embbed my database with the application in some way.
thanks and iam waiting ur replies
its urgent plzzzzzzzzzz
|
|
|
|
|
You can use SQL Server Express, if you want to distribute something that is free. Or move to Access.
Mdallal84 wrote: in other words should MS SQL Server 2000 be installed on the machine to make the application running ?
Yes, and once it's installed, you need to run scripts to create the database on the SQL Server instance, and make sure it's installed correctly in relation to your connection string ( it could be a named instance, for example )
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Would an application developed in .NET Framework 1.1 and using MSDE be able to work with SQL Server 2005 Express edition which uses .NET 2.0?? Also MSDE is not supported in Vista, does that mean applications using MSDE previously (and .NET 1.1) will not work on Vista?
Also i have restored a DB with some existing users in SQL Express .Now i select Security>Users and try to delete the existing user but it gives me a strange exception like:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for User 'test_user'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15138&LinkId=20476
------------------------------
Any idea?
@!$(-)@ $@r£r@Z
|
|
|
|
|
Aisha Ikram wrote: Would an application developed in .NET Framework 1.1 and using MSDE be able to work with SQL Server 2005 Express edition which uses .NET 2.0??
Yes, 2005 uses .Net 2.0 for CLR Stored Procs and stuff like that. What your application uses has nothing to do with it 
Aisha Ikram wrote: Also MSDE is not supported in Vista, does that mean applications using MSDE previously (and .NET 1.1) will not work on Vista?
No it means the MSDE won't run under Vista, your application can run on Vista and connect to the MSDE running on a different machine.
|
|
|
|
|
Thanks for the response.
originSH wrote: No it means the MSDE won't run under Vista, your application can run on Vista and connect to the MSDE running on a different machine.
Yes, I understand this but MSDE is meant to be used locally/personally. I don't understand why Vista not supporting this ? is there any way to make that work on Vista?
@!$(-)@ $@r£r@Z
|
|
|
|
|
MSDE is not supported on vista because it is free and Sql Server 2005 Express has taken over from it.
Theres no point putting more effort into MSDE when 2005 Express does everything that MSDE does and more.
Your application should work perfectly well with 2005 Express so you should be able to just do a straight swap.
(Note: well almost the only thing that might break is if you use case insensative passwords. Passwords are now case sensative in 2005)
|
|
|
|
|
thanks a lot for your response.
Any idea about my second question that why i am geeting this message:
Also i have restored a DB with some existing users in SQL Express .Now i select Security>Users and try to delete the existing user but it gives me a strange exception like:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for User 'test_user'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15138&LinkId=20476
------------------------------
@!$(-)@ $@r£r@Z
|
|
|
|
|
Hello,
VS 2005
I have a combo box that is bounded to a combo box.
The combo box will load orders into it.
I want to be able to insert "All" into the first element:
Me.cboOrdersCompleted.Items.Insert(0, "All")
However, as the combo box is bound to a binding source it won't all me to insert this word.
The method I have used to fill the combo box is as follows:
Me.cboOrdersCompleted.DataSource = Me.bsOrders<br />
Me.cboOrdersCompleted.DisplayMember = "OrderID"<br />
Me.cboOrdersCompleted.ValueMember = "OrderID"
My problem is when I insert the word it does this without any errors, but doesn't show. If I insert the word after the combo has been filled, then it will give me the error that the "Items cannot be modified once the combo is bound to a datasource"
Is there anything I can do to add this "All" into the combo box?
Many thanks for any suggestions,
Steve
|
|
|
|
|
Manage the bindng yourself. I have never used MS's binding due to its limitations.
|
|
|
|
|
Hi,
I am using an html input box to populate a variable, which is in turn used as a date in a query that interrogates my database.
The problem I am having is that the date needs to be in the format "yyyy/mm/dd hh:mm:ss" for the sql query to work and I cannot find any way of converting the date into this format (I can only seem to convert it to dd/mm/yyyy or mm/dd/yyyy).
can anyone help??
thanks
|
|
|
|
|
Why do you have to have the hours/minutes/seconds for the query to work? Is it used later on?
______________________
stuff + cats = awesome
|
|
|
|
|
You can specify exact formatting for a datetime by using
Dim formattedDate as string = myDate.ToString("yyyy/MM/dd hh:mm:ss")
|
|
|
|
|
In reply to both the posts so far:
1) I do not neeed the time - it's just the date in the correct format.
2) using the .ToString has come up with the following error:
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/Home/Act_report_packs_in.asp, line 19
Dim formattedDate as string = DateTo.ToString("yyyy/MM/dd hh:mm:ss")
------------------^
Is this .net specific??
I have managed to work around it by using the left(), mid() and Right() functions on the string and then re-arranging them, but it would still be nice to have a 'neater' way of doing it??!
thanks
|
|
|
|
|
Normal rules apply: for values that can change, particularly ones supplied by the user, you should use a parameterized query. You should only break this rule if your database provider doesn't support parameterized queries.
In classic ADO, use the Command object's Parameters collection. See your provider's documentation for how to indicate a parameter (for SQL Server, you can use named parameters by using an @ symbol followed by a name). Some providers only support positional parameters.
For a date/time value, you probably want the adDBTimeStamp data type for your parameter. You'll have to convert the value in your input box into a VBScript Date value. For that you probably need the DateSerial function.
|
|
|
|
|
I am creating an advanced search page.in that I have 11 fields.i wrote a stored procedure which has all 11 parameters.
If I don’t enter any value for the parameters I am not getting the result and it goes to exception handling.
Now what I want is even I enter one value this must be excute………….i think some validations must applied to Stored procedure's where clause.but I don’t know how …………….can anyone help me regarding this
would be thankful if you send the code for this
Thanks
sri
|
|
|
|
|
These type of queries can get messy quick. Normally you need one table that will always be returned no matter what is being searched on. So that is the first table in the statement. All other tables that are optional need to be left joins to that first table other wise you won't get any rows returned. Then in your where clause you have to do some stuff like:
where (@param1 = null || table2.column = @param1)
It can be a real mess. The only other option is to dynamically create your query in your stored procedure and then call Exec on that sql statement.
Hope that helps.
Ben
|
|
|
|
|
AS:1
QNO : 1
CREATE TABLE MEMBER_VIJAY(MEMBER_ID NUMBER(5),
MEMBER_NAME CHAR(25),
ACC_OPEN_DATE DATE,
MAX_BOOKS_ALLOWED NUMBER(2),
PENALTY_AMOUNT NUMBER(7,2)
);
CREATE TABLE BOOK_VIJAY(BOOK_NO NUMBER(6),
BOOK_NAME VARCHAR2(30),
AUTHOR CHAR(30),
COST NUMBER(7,2),
CATEGORY CHAR(10)
);
CREATE TABLE ISSUE_VIJAY(LIB_ISSUE_ID NUMBER(10),
BOOK_NO NUMBER(6),
MEMBER_ID NUMBER(5),
ISSUE_DATE DATE,
RETURN_DATE DATE
);
QNO : 2
DESC MEMBER_VIJAY;
DESC BOOK_VIJAY;
DESC ISSUE_VIJAY;
QNO : 3
ALTER TABLE ISSUE_VIJAY ADD( COMMENTS CHAR(100));
QNO : 4
ALTER TABLE MEMBER_VIJAY MODIFY(MEMBER_NAME CHAR(30));
QNO : 5
ALTER TABLE ISSUE_VIJAY ADD(REFERENCE CHAR(30));
QNO : 6
ALTER TABLE ISSUE_VIJAY DROP COLUMN REFERENCE;
QNO : 7
RENAME ISSUE_VIJAY TO LIB_ISSUE_VIJAY;
QNO : 8
INSERT INTO MEMBER_VIJAY VALUES(1,'RICHA SHARMA','10-DEC-05',5,50);
INSERT INTO MEMBER_VIJAY VALUES(2,'GARIMA SHARMA',SYSDATE,3,NULL);
QNO : 9
ALTER TABLE MEMBER_VIJAY MODIFY(MEMBER_NAME CHAR(20));
ORA-01441: cannot decrease column length because some value is too big
QNO : 10
INSERT INTO MEMBER_VIJAY VALUES(1,'VIJAY','27-JUNE-2007',110,100);
ORA-01438: value larger than specified precision allows for this column
QNO : 11
CREATE TABLE MEMBER101_VIJAY AS (SELECT *FROM MEMBER_VIJAY);
QNO : 12
INSERT INTO BOOK_VIJAY VALUES(101,'LET US C','DENIS RITCHIE',450,'SYSTEM');
INSERT INTO BOOK_VIJAY VALUES(102,'ORACLE-COMPLETE REFERENCE','LONI',550,'DATABASE');
INSERT INTO BOOK_VIJAY VALUES(103,'MASTERING SQL','LONI',250,'DATABASE');
INSERT INTO BOOK_VIJAY VALUES(104,'PL SQL-REF','SCOTT URMAN',750,'DATABASE');
QNO : 13
INSERT INTO BOOK_VIJAY VALUES(&BOOK_NO,'&BOOK_NAME','&AUTHOR',&COST,'&CATEGORY');
Enter value for book_no: 105
Enter value for book_name: C++
Enter value for author: LIPMAN
Enter value for cost: 450
Enter value for category: SYSTEM
QNO : 14
CREATE TABLE BOOK101_VIJAY AS (SELECT BOOK_NO,BOOK_NAME,AUTHOR,COST,CATEGORY FROM BOOK_VIJAY WHERE BOOK_NO NOT LIKE '1%');
QNO : 15
INSERT INTO BOOK101_VIJAY(SELECT *FROM BOOK_VIJAY);
QNO : 16
COMMIT;
QNO : 17
SELECT *FROM MEMBER_VIJAY;
SELECT *FROM BOOK_VIJAY;
SELECT *FROM ISSUE_VIJAY;
SELECT *FROM MEMBER101_VIJAY;
SELECT *FROM BOOK101_VIJAY;
QNO : 18
INSERT INTO BOOK_VIJAY VALUES(105,'NATIONAL GEOGRAPHIC','ADIS SCOTT',1000,'SCIENCE');
QNO : 19
ROLLBACK;
QNO : 20
UPDATE BOOK_VIJAY SET COST=300,CATEGORY='RDBMS' WHERE BOOK_NO=103;
QNO : 21
RENAME TABLE LIB_ISSUE_VIJAY TO ISSUE_VIJAY;
QNO : 22
INSERT INTO ISSUE_VIJAY(LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE)
VALUES(&LIB_ISSUE_ID,&BOOK_NO,&MEMBER_ID,&ISSUE_DATE);
Enter value for lib_issue_id: 7001
Enter value for book_no: 101
Enter value for member_id: 1
Enter value for issue_date: '10-DEC-06'
QNO : 23
COMMIT;
QNO : 24
INSERT INTO ISSUE_VIJAY(LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE)
VALUES(&LIB_ISSUE_ID,&BOOK_NO,&MEMBER_ID,&ISSUE_DATE);
Enter value for lib_issue_id: 7007
Enter value for book_no: 101
Enter value for member_id: 3
Enter value for issue_date: '10-JUNE-06'
INSERT INTO ISSUE_VIJAY(LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE)
VALUES(&LIB_ISSUE_ID,&BOOK_NO,&MEMBER_ID,&ISSUE_DATE);
Enter value for lib_issue_id: 7007
Enter value for book_no: 102
Enter value for member_id: 2
Enter value for issue_date: '15-JULY-06'
QNO : 25
UPDATE ISSUE_VIJAY SET RETURN_DATE=(ISSUE_DATE+15) WHERE LIB_ISSUE_ID=7004
OR LIB_ISSUE_ID=7005;
QNO : 26
UPDATE MEMBER_VIJAY SET PENALTY_AMOUNT=100 WHERE MEMBER_NAME='GARIMA SHARMA';
QNO : 27
SAVEPOINT X;
QNO : 28
DELETE FROM ISSUE_VIJAY WHERE MEMBER_ID=1 AND ISSUE_DATE<'10-DEC-06';
QNO : 29
DELETE FROM BOOK_VIJAY WHERE CATEGORY!='RDBMS' AND CATEGORY!='DATABASE';
QNO : 30
ROLLBACK TO X;
QNO : 31
COMMIT;
QNO : 32
DROP TABLE MEMBER101_VIJAY;
QNO : 33
DROP TABLE BOOK101_VIJAY;
QNO : 34
SELECT * FROM MEMBER_VIJAY;
SELECT * FROM BOOK_VIJAY;
SELECT *FROM ISSUE_VIJAY;
DESC MEMBER_VIJAY;
DESC BOOK_VIJAY;
DESC ISSUE_VIJAY;
QNO : 35
SELECT TNAME FROM TAB WHERE TNAME LIKE '%VIJAY';
########################################################################
AS: 2
QNO : 1
SELECT MEMBER_NAME
FROM MEMBER
WHERE ACC_OPEN_DATE>'31-DEC-2005' AND ACC_OPEN_DATE <'1-JAN-2007';
OR
SELECT MEMBER_NAME
FROM MEMBER
WHERE ACC_OPEN_DATE BETWEEN '31-DEC-2005' AND '1-JAN-2007';
QNO : 2
SELECT BOOK_NAME,AUTHOR
FROM BOOK
WHERE AUTHOR='LONI' AND COST>600;
QNO : 3
SELECT LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE,RETURN_DATE
FROM ISSUE
WHERE COMMENTS='PENDING';
QNO : 4
UPDATE ISSUE SET RETURN_DATE='31-DEC-06'
WHERE RETURN_DATE=NULL AND (LIB_ISSUE_ID!=7005 OR LIB_ISSUE_ID!=7006);
QNO : 5
SELECT LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE,RETURN_DATE
FROM ISSUE
WHERE (SYSDATE-ISSUE_DATE)>30 AND (ISSUE_DATE-RETURN_DATE)>30;
OR
SELECT LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE,RETURN_DATE
FROM ISSUE
WHERE ((SYSDATE-ISSUE_DATE)>30 AND RETURN_DATE=NULL) OR (ISSUE_DATE-RETURN_DATE)>30;
QNO : 6
SELECT BOOK_NAME
FROM BOOK
WHERE (COST BETWEEN 500 AND 750) AND CATEGORY='DATABASE';
QNO : 7
SELECT BOOK_NAME
FROM BOOK
WHERE CATEGORY IN('SCIENCE','DATABASE','FICTION','MANAGEMENT');
QNO : 8
SELECT MEMBER_ID,MEMBER_NAME,PENALTY_AMOUNT
FROM MEMBER
ORDER BY PENALTY_AMOUNT DESC;
OR
SELECT MEMBER_ID,MEMBER_NAME,PENALTY_AMOUNT
FROM MEMBER
ORDER BY 3 DESC;
QNO : 9
SELECT BOOK_NAME,COST
FROM BOOK
ORDER BY BOOK_NAME,COST DESC;
OR
SELECT BOOK_NAME,COST
FROM BOOK
ORDER BY 1,2 DESC;
QN0 : 10
SELECT BOOK_NAME
FROM BOOK
WHERE BOOK_NAME LIKE '%SQL%';
QNO : 11
SELECT MEMBER_NAME
FROM MEMBER
WHERE MEMBER_NAME LIKE ('G%I%' ||'R%I%');
QNO : 12
SELECT INITCAP(BOOK_NAME),UPPER(AUTHOR)
FROM BOOK
ORDER BY 1 DESC;
QNO : 13
SELECT BOOK_NO, CONCAT('IS WRRITTEN ', 'BY') " ---------------- ", AUTHOR AS NAME
FROM BOOK;
OR
SELECT CONCAT( BOOK_NO,' IS WRITTEN BY') "BOOKNO ------------------",AUTHOR AS NAME
FROM BOOK;
QNO : 14
SELECT LIB_ISSUE_ID,BOOK_NO,TO_CHAR(TO_DATE(ISSUE_DATE,'DD-MM-YY'),'Day,Month,DD,YYYY') ,
TO_CHAR(TO_DATE(RETURN_DATE,'DD-MM-YY'),'Day,Month,DD,YYYY')
FROM ISSUE
WHERE MEMBER_ID=101;
QNO : 15 & 16
SELECT CATEGORY,
DECODE(CATEGORY,'DATABASE','D','SCIENCE','S','RDBMS','R','OTHERS','O')
FROM BOOK;
QNO : 17
SELECT CONCAT( RPAD(BOOK_NAME,20,'*'),LPAD(AUTHOR,20,'*') )
FROM BOOK;
QNO : 18
SELECT LIB_ISSUE_ID,ISSUE_DATE,RETURN_DATE,COUNT(ISSUE_DATE-RETURN_DATE) "NO OF DAYS"
FROM ISSUE;
QNO : 19
SELECT MEMBER_ID,MEMBER_NAME,ACC_OPEN_DATE,MAX_BOOKS_ALLOWED,PENALTY_AMOUNT
FROM MEMBER
ORDER BY 3 DESC;
QNO : 20
SELECT COUNT(BOOK_NO)
FROM ISSUE
WHERE MEMBER_ID=101;
QNO : 21
SELECT SUM(PENALTY_AMOUNT)
FROM MEMBER;
QNO : 22
SELECT SUM(COST)
FROM BOOK
WHERE CATEGORY='DATABASE';
QNO : 23
SELECT BOOK_NAME,COST
FROM BOOK ;
QNO : 24
SELECT BOOK_NO,MIN(ISSUE_DATE)
FROM ISSUE;
QNO : 25
SELECT BOOK_NO,MAX(ISSUE_DATE)
FROM ISSUE;
QNO : 26
SELECT AVG(COST)
FROM BOOK
WHERE CATEGORY='DATABASE';
########################################################################
AS: 3
QNO : 1
SELECT CATEGORY,COUNT(BOOK_NO) FROM BOOK_C_VIJAY GROUP BY CATEGORY;
QNO : 2
SELECT B.BOOK_NO,COUNT(I.BOOK_NO) FROM BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE (B.BOOK_NO=I.BOOK_NO) GROUP BY B.BOOK_NO;
QNO : 3
SELECT MAX(PENALTY_AMOUNT),MIN(PENALTY_AMOUNT),SUM(PENALTY_AMOUNT),
AVG(PENALTY_AMOUNT) FROM MEMBER_C_VIJAY;
QNO : 4
SELECT M.MEMBER_ID,COUNT(I.MEMBER_ID) FROM
MEMBER_C_VIJAY M,ISSUE_C_VIJAY I WHERE
(M.MEMBER_ID=I.MEMBER_ID) GROUP BY M.MEMBER_ID;
QNO : 5
SELECT I.MEMBER_ID,B.BOOK_NO,COUNT(I.BOOK_NO)
FROM ISSUE_C_VIJAY I,BOOK_C_VIJAY B
WHERE I.BOOK_NO IN(B.BOOK_NO)
GROUP BY I.MEMBER_ID,B.BOOK_NO;
QNO : 6
SELECT TO_CHAR(ISSUE_DATE,'MONTH'),COUNT(BOOK_NO)
FROM ISSUE_C_VIJAY
GROUP BY TO_CHAR(ISSUE_DATE,'MONTH')
ORDER BY 2 DESC;
QNO : 7
SELECT BOOK_NO FROM BOOK_C_VIJAY
WHERE BOOK_NO NOT IN(SELECT BOOK_NO FROM ISSUE_C_VIJAY);
QNO : 8
SELECT MEMBER_ID FROM MEMBER_C_VIJAY
WHERE MEMBER_ID IN(SELECT MEMBER_ID FROM ISSUE_C_VIJAY);
QNO : 9
SELECT MEMBER_ID,COUNT(*)
FROM ISSUE_VIJAY
GROUP BY MEMBER_ID
HAVING COUNT(*) IN
( (SELECT MAX( COUNT(*) ) FROM ISSUE_VIJAY
GROUP BY MEMBER_ID),
(SELECT MIN( COUNT(*) ) FROM ISSUE_VIJAY
GROUP BY MEMBER_ID) );
QNO : 10
SELECT * FROM ISSUE_C_VIJAY
WHERE TO_CHAR(ISSUE_DATE,'MM') IN('12','07');
QNO : 11
SELECT B.BOOK_NO,B.BOOK_NAME,I.ISSUE_DATE FROM
BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE TO_CHAR(ISSUE_DATE,'MM') IN('12') AND B.BOOK_NO=I.BOOK_NO AND CATEGORY='DATABASE';
QNO : 12
SELECT I.MEMBER_ID,M.MEMBER_NAME,COUNT(I.BOOK_NO)
FROM MEMBER_C_VIJAY M,ISSUE_C_VIJAY I
WHERE I.MEMBER_ID=M.MEMBER_ID
GROUP BY I.MEMBER_ID,M.MEMBER_NAME
ORDER BY 3 DESC;
QNO : 13
SELECT B.BOOK_NO,B.BOOK_NAME,I.ISSUE_DATE,I.RETURN_DATE
FROM BOOK_C_VIJAY B,MEMBER_C_VIJAY M,ISSUE_C_VIJAY I
WHERE (M.MEMBER_NAME='VIJAY' AND I.BOOK_NO=B.BOOK_NO
AND I.MEMBER_ID=M.MEMBER_ID);
QNO : 14
SELECT M.MEMBER_ID,M.MEMBER_NAME,B.CATEGORY
FROM MEMBER_C_VIJAY M,BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE B.CATEGORY='DATABASE' AND M.MEMBER_ID=I.MEMBER_ID
AND B.BOOK_NO=I.BOOK_NO;
QNO : 15
SELECT CATEGORY, MAX(COST)
FROM BOOK_C_VIJAY
GROUP BY CATEGORY;
QNO : 16
SELECT M.ACC_OPEN_DATE,I.BOOK_NO,I.MEMBER_ID,I.ISSUE_DATE,I.RETURN_DATE
FROM MEMBER_C_VIJAY M,ISSUE_C_VIJAY
I WHERE (I.ISSUE_DATE NOT BETWEEN M.ACC_OPEN_DATE AND I.RETURN_DATE)
AND I.MEMBER_ID=M.MEMBER_ID ;
QNO : 17
SELECT DISTINCT M.MEMBER_ID,M.MEMBER_NAME
FROM MEMBER_C_VIJAY M,ISSUE_C_VIJAY
WHERE M.MEMBER_ID NOT IN(SELECT MEMBER_ID FROM ISSUE_C_VIJAY);
QNO : 18
SELECT MEMBER_ID,MEMBER_NAME
FROM MEMBER_C_VIJAY
WHERE
(SELECT COUNT(I.BOOK_NO) FROM ISSUE_C_VIJAY I,BOOK_C_VIJAY B,MEMBER_C_VIJAY M
WHERE I.BOOK_NO=B.BOOK_NO AND I.MEMBER_ID=M.MEMBER_ID)>MAX_ALLOWED_BOOKS
GROUP BY MEMBER_ID,MEMBER_NAME;
QNO : 19
QNO : 20
SELECT DISTINCT B.BOOK_NAME,B.COST
FROM BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE (I.RETURN_DATE-I.ISSUE_DATE)>30;
QNO : 21
SELECT B.AUTHOR,B.BOOK_NAME FROM BOOK_C_VIJAY B
WHERE (SELECT COUNT(C.AUTHOR) FROM BOOK_C_VIJAY C WHERE B.AUTHOR=C.AUTHOR)>=2;
QNO : 22
SELECT M.MEMBER_NAME,M.MEMBER_ID,COUNT(*)
FROM ISSUE_C_VIJAY I,MEMBER_C_VIJAY M
WHERE M.MEMBER_ID = I.MEMBER_ID
GROUP BY M.MEMBER_NAME,M.MEMBER_ID
HAVING COUNT(*) IN
( (SELECT MAX( COUNT(*) ) FROM ISSUE_C_VIJAY GROUP BY MEMBER_ID),
(SELECT MIN( COUNT(*) ) FROM ISSUE_C_VIJAY GROUP BY MEMBER_ID) ) ;
QNO : 23
SELECT MAX(COST) FROM BOOK_C_VIJAY
UNION
SELECT MAX(COST) FROM BOOK_C_VIJAY
WHERE COST < (SELECT MAX(COST) FROM BOOK_VIJAY)
UNION
SELECT MAX(COST) FROM BOOK_C_VIJAY
WHERE COST < ( SELECT MAX(COST) FROM BOOK_C_VIJAY WHERE COST < (SELECT MAX(COST) FROM BOOK_C_VIJAY) );
OR
SELECT BOOK_NAME,AUTHOR,COST FROM BOOK_C_VIJAY
ORDER BY COST DESC;
QNO : 24
SELECT SUM(B.COST) FROM BOOK_C_VIJAY B,
ISSUE_C_VIJAY I
WHERE (I.RETURN_DATE>SYSDATE AND B.BOOK_NO=I.BOOK_NO);
QNO : 25
SELECT B.BOOK_NO,B.BOOK_NAME,COUNT(*)
FROM ISSUE_C_VIJAY I,BOOK_C_VIJAY B
WHERE B.BOOK_NO=I.BOOK_NO
GROUP BY B.BOOK_NO,B.BOOK_NAME
HAVING COUNT(*) IN(SELECT MAX( COUNT(*) ) FROM ISSUE_C_VIJAY GROUP BY MEMBER_ID);
#####################################################################
AS : 4
QNO : 1
CREATE TABLE MEMBER_C_VIJAY(MEMBER_ID NUMBER(5) PRIMARY KEY,
ACC_OPEN_DATE DATE DEFAULT SYSDATE,
MAX_ALLOWED_BOOKS NUMBER(2) CHECK(MAX_ALLOWED_BOOKS<100),
PENALTY_AMOUNT NUMBER(7,2) CHECK(PENALTY_AMOUNT<=1000)
MEMBER_NAME CHAR(25));
QNO : 2
CREATE TABLE BOOK_C_VIJAY(BOOK_NO NUMBER(6) PRIMARY KEY,
BOOK_NAME VARCHAR2(30) NOT NULL, CATEGORY CHAR(10)
CHECK(CATEGORY='SCIENCE' OR CATEGORY='FICTION' OR CATEGORY='FICTION' OR CATEGORY='DATABASE' OR CATEGORY='RDBMS' OR CATEGORY='OTHERS'))
QNO : 3
CREATE TABLE ISSUE_C_VIJAY(LIB_ISSUE_ID NUMBER(10) PRIMARY KEY,
MEMBER_ID NUMBER(5) REFERENCES MEMBER_C_VIJAY(MEMBER_ID),
BOOK_NO NUMBER(6) REFERENCES BOOK_C_VIJAY(BOOK_NO),
ISSUE_DATE DATE,
RETURN_DATE DATE );
ALTER TABLE ISSUE_C_VIJAY ADD CONSTRAINT I CHECK(RETURN_DATE>ISSUE_DATE);
OR
CREATE TABLE ISSUE_C_VIJAY(LIB_ISSUE_ID NUMBER(10) PRIMARY KEY,
MEMBER_ID NUMBER(5) REFERENCES MEMBER_C_VIJAY(MEMBER_ID),
BOOK_NO NUMBER(6) REFERENCES BOOK_C_VIJAY(BOOK_NO),
ISSUE_DATE DATE,
RETURN_DATE DATE ,CHECK(RETURN_DATE>ISSUE_DATE);
QNO : 4
ALTER TABLE BOOK_C_VIJAY ADD CONSTRAINT V CHECK(COST<2500);
QNO : 5
SELECT *FROM USER_CONSTRAINTS WHERE TABLE_NAME='ISSUE_C_VIJAY';
QNO : 6
ALTER TABLE ISSUE_C_VIJAY DISABLE CONSTRAINT I;
QNO : 7
CREATE VIEW Vijay AS( SELECT LIB_ISSUE_ID,BOOK_NAME,MEMBER_NAME,AUTHOR,ISSUE_DATE
FROM
BOOK_C_VIJAY B,MEMBER_C_VIJAY M,ISSUE_C_VIJAY I
WHERE ((B.COST>500 AND B.COST<750) AND (B.CATEGORY='RDBMS' OR B.CATEGORY='DATABASE') AND
I.BOOK_NO=B.BOOK_NO AND I.MEMBER_ID=M.MEMBER_ID));
QNO : 8
CREATE VIEW VIJAY1 AS(SELECT BOOK_NAME FROM BOOK_C_VIJAY WHERE COST>500);
QNO : 9
QNO : 10
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)