SQL help needed.

[[N]auTiCa]

Heyy.. naut back here asking for assistance again. It's been almost a yr since I last asked for help lol.

Anways, anyone here good at SQL?

I need some help with a program and have to get this done in a couple days, so if anyone is up for helping please let me know asap and I will post what I'm having trouble with.

[[N]auTiCa]

anyone know how to use isql*plus?

specifically, how to enter in data and execute it to display results. I can create a table, but try and execute it and get nothing, unless I'm doing it wrong, been following the tutorial at http://www.oracle.com/technology/obe/obe9ir2/obe-mng/isqlplus/isqlplus.htm

console (of all trades).. do you know any sql?

[console]

console (of all trades).. do you know any sql?

I know the basic create drop insert update select syntax...  Nothing about "isqlplus" though.  I typically send SQL to database engines from programming languages.  (Perl, Ruby, etc.)


Regards,

[[BTF]Defiant!]

anyone know how to use isql*plus?

specifically, how to enter in data and execute it to display results. I can create a table, but try and execute it and get nothing, unless I'm doing it wrong, been following the tutorial at http://www.oracle.com/technology/obe/obe9ir2/obe-mng/isqlplus/isqlplus.htm

console (of all trades).. do you know any sql?

If you can create the table then you should be able to query against it.  Do you have any data in the table?  Can you screenshot a before and after?

[[N]auTiCa]

OK, I have created these tables

Code: [Select]

CREATE TABLE Student
(studentid INT,
firstname CHAR(20),
lastname CHAR(20),
grade CHAR(1),
dept CHAR(25),
DOB DATE,
fine REAL,
PRIMARY KEY(firstname);

CREATE TABLE Book
(isbn INT,
title CHAR(20),
author CHAR(20),
edition INT,
bookyear DATE)

CREATE TABLE Borrowed
(studentid INT,
isbn INT,
borrowdate DATE,
due DATE)

Then I have to create 10 tuples with data for each. After that I am to retrieve data.. but will worry about that later, for now I want to know how to make up data for the tables, any ideas?

[reaper]

General format:
create database
create tables
create fields
grant privledges

populate database
query database

I use mysql, phpmysqladmin (for a gui admin interface), and usually populate the database with user input, or a feed from a named pipe.

I don't u se iSQL, but from glancing over your link, it looks pretty similar to mysql.

[[N]auTiCa]

Thanks reaper.. I was looking for the INSERT cmd, wasn't sure how to input generic data to run a query.

I'm working on it now and will show my results in a little bit.

[[N]auTiCa]

Anyone know the cmd to find out how many records there are of a specific person.

Say you want to find out how many students have the first name mike.

[console]

Anyone know the cmd to find out how many records there are of a specific person.

Say you want to find out how many students have the first name mike.

http://www.techonthenet.com/sql/count.php

select count(*) from students where first_name like 'mike';

[[N]auTiCa]

I tried googlin' it with no luck..

thanks console.

[[N]auTiCa]

Another question, say you want to find the person who has rented the most movies from the year 2000 to 2007.

Do you perform a count first, then compare it and display results?

Stuck on this one for the last half hour.. any info would be appreciated.

[console]

Another question, say you want to find the person who has rented the most movies from the year 2000 to 2007.

Do you perform a count first, then compare it and display results?

Depends on what your tables look like.  What sort of records keep track of movie rentals?  Is it like a row for each movie rented, with a userid and a date?

[[N]auTiCa]

Well its actually trying to find the highest borrowing record for a students last name for the year 2000-2007

my tables are below with some sample data.

Code: [Select]

CREATE TABLE STUDENT
(STUID INTEGER PRIMARY KEY,
FNAME CHAR(20),
LNAME CHAR(20),
GRADE CHAR(2),
DEPT CHAR(25),
DOB CHAR(10),
FINE REAL);

-- Book table
CREATE TABLE BOOK
(ISBN INTEGER PRIMARY KEY,
TITLE CHAR(30),
AUTHOR CHAR(20),
EDITION CHAR(5),
BOOKYR CHAR(4));

-- Borrowed table
CREATE TABLE BORROWED
(STUID INTEGER,
ISBN INTEGER,
BORROWDATE CHAR(10),
DUE CHAR(10)
PRIMARY KEY(STUID, ISBN, BORROWDATE));

-- Sample data for STUDENT
INSERT INTO STUDENT VALUES ('4567', 'Michael', 'Doe', 'C', 'English', '02/15/1982', '20.00')

-- Sample data for BORROWED
INSERT INTO BORROWED VALUES ('4567', '3456345798', '10/05/2007', '11/05/2007')


What do you think?

[[N]auTiCa]

Do you think this would work? I can't test any of these statements as I don't have access to a database, but the logic seems right

Code: [Select]

Select LNAME, COUNT(LNAME)
 FROM BORROWED GROUP BY LNAME, DEPT
 HAVING COUNT(LNAME)=(SELECT MAX(A.CNT)
 FROM (SELECT COUNT(LNAME) AS CNT FROM STUDENT
 GROUP BY (LNAME, DEPT)) AS A);


[console]

Hmm.  Not really sure.  It doesn't quite look right to me, but I don't write queries like these often, and when I do I have to consult my SQL book each time.

BTW, why don't you have access to a database?  You could install sqlite3 or mysql or postgres on your local system.  Wouldn't that work?


Regards,

quadz