Hiding a Subreport: statements

Showing posts with label statements. Show all posts

Thursday, March 29, 2012

Homework assignment

I am in a college system development class, and we are using SQLPlus with some tables and data loaded to answer questions with SQL statements. I am stuck on a few questions. I was able to answer 30 of the 40 using the references. The questions have gotten very difficult in my opinion now. I write the statements, but I am not getting what the question asks for. Here is one that I am stuck on now. Here is the tables, so you know what fields there are to select from.

/* create AIRPORT */

Drop table airport;

create table AIRPORT
( air_code varchar2 (3)
CONSTRAINT airport_code_pk PRIMARY KEY,
air_location varchar2 (35),
elevation number (4,0),
air_phone varchar2 (12),
service_center char (1),
airpt_type_code char(2)
);

/* CREATE AIRPORT CLASS */

drop table airpt_class;

create table airpt_class
(airpt_type_code char(2)
constraint air_type_pk PRIMARY KEY,
airpt_type_descr varchar2 (25) );

/* CREATE Frequent_flyer */

drop table frequent_flyer;

create table frequent_flyer
(ff_no number(4)
constraint freq_flyer_pk primary key,
L_name varchar2(15) not null,
F_name varchar2(15) not null,
Str_Address varchar2(30),
City varchar2(25),
State char(2),
zip_code char(5),
total_miles number (8),
cur_year_miles number (6),
first_pur_date date);

/* CREATE FLIGHT */

drop table flight;

create table FLIGHT
(
flight_no number (4)
CONSTRAINT flight_flight_no_pk PRIMARY KEY,
orig varchar2 (3),
dest varchar2 (3),
orig_time date,
dest_time date,
meal varchar2 (1),
fare number (7,2),
ff_miles number (4)
);

/* CREATE RESERVATION */

drop table reservation;

create table reservation
(
confirm_no number (4),
res_date date,
res_name varchar2 (20),
res_phone varchar2 (12),
paid_flag char (1),
CONSTRAINT reservation_confirm_no_pk
PRIMARY KEY (confirm_no)
);

/* CREATE PASSENGER */

drop table passenger;

create table passenger
(
pass_name varchar2 (20)
CONSTRAINT passenger_pass_name_nn NOT NULL,
itinerary_no number (4)
CONSTRAINT passenger_itin_no_pk PRIMARY KEY,
ff_no number (4),
confirm_no number (4) NOT NULL
);

/* CREATE TICKET */

drop table ticket;

create table ticket
(
itinerary_no number (4)
CONSTRAINT ticket_itin_no_nn NOT NULL,
flight_no number (4)
CONSTRAINT ticket_flight_no_nn NOT NULL,
flight_date date,
seat varchar2 (3),
fare_charged number(7,2),
CONSTRAINT ticket_itin_fltno_fltdate_pk
PRIMARY KEY (itinerary_no, flight_no, flight_date),
CONSTRAINT ticket_itinno_fk
FOREIGN KEY (itinerary_no)
REFERENCES passenger
ON DELETE CASCADE,
CONSTRAINT ticket_fltno_fk
FOREIGN KEY (flight_no)
REFERENCES flight
ON DELETE CASCADE
);

/* CREATE SEAT */

drop table seat;

create table seat
(equip_type varchar2(6),
seat varchar2(3)
);

/* CREATE STAFF */

drop table staff;

create table staff
(
staff_no number (4),
staff_name varchar2 (20),
staff_type varchar2 (20),
staff_date date,
CONSTRAINT staff_staff_no_pk PRIMARY KEY (staff_no)
);

/* CREATE FLIGHT_DAY */

/* drop table */
drop table flight_day;

/* create table */
create table flight_day
(
flight_no number (4)
CONSTRAINT flight_day_flight_no_nn NOT NULL,
flight_date date
CONSTRAINT flight_day_flight_date_nn NOT NULL,
equip_no number (3,0),
pass_ctr integer,
depart_time date,
arrive_time date,
fuel_used integer,
status_note varchar2 (20),
CONSTRAINT flight_day_no_date_pk
PRIMARY KEY (flight_no, flight_date),
CONSTRAINT flight_day_flight_no_fk
FOREIGN KEY (flight_no)
REFERENCES flight
ON DELETE CASCADE
);

/* CREATE FLIGHT_CREW */

drop table flight_crew;

create table flight_crew
(
flight_no number (4)
CONSTRAINT flight_crew_flight_no_nn NOT NULL,
flight_date date
CONSTRAINT flight_crew_flight_date_nn NOT NULL,
staff_no number (4)
CONSTRAINT flight_crew_staff_no_nn NOT NULL,
work_hrs number (4,1),
CONSTRAINT flight_crew_fltno_date_staf_pk
PRIMARY KEY (flight_no, flight_date, staff_no),
CONSTRAINT flight_crew_fltno_date_fk
FOREIGN KEY (flight_no, flight_date)
REFERENCES flight_day
ON DELETE CASCADE,
CONSTRAINT flight_crew_staffno_fk
FOREIGN KEY (staff_no)
REFERENCES staff
ON DELETE CASCADE
);

/* create table equipment */

drop table equipment;

create table equipment
( equip_no number (3,0)
CONSTRAINT equipment_no_pk PRIMARY KEY,
equip_type varchar2 (6),
equip_date date
);

/* CREATE EQUIP_TYPE */

drop table equip_type;

create table equip_type
( equip_type varchar2 (6)
CONSTRAINT equip_type_type_pk PRIMARY KEY,
mfg varchar2 (15),
seats number (3),
fuel_cap number
);

/* CREATE NOTES */

drop table notes;

create table notes
(
confirm_no integer not null PRIMARY KEY,
note varchar2 (50),
CONSTRAINT notes_confirm_no_fk
FOREIGN KEY (confirm_no)
REFERENCES reservation
ON DELETE CASCADE
);

/* ADD FOREIGN KEYS TO FLIGHT */

alter table flight
add constraint orig_for_key
foreign key (orig) references airport;
alter table flight
add constraint dest_for_key
foreign key (dest) references airport;

The question wants - the people's name with reservations, flight numbers, reservation date, and the flight date for all reservations between 30 and 60 days in advance in 2002. The professor wants us to join the reservation.confirm_no to the ticket.Itinerary_no. Make it a select distinct query to keep it to 22 rows coming back.

I am getting the reservation date after the flight date with my statement. I wrote
SELECT DISTINCT reservation.res_date, reservation.Res_name, ticket.flight_date, ticket.flight_d
ate-30 AS "ADVANCE NOTICE"
2 FROM reservation, ticket
3 WHERE ticket.itinerary_no=reservation.confirm_no
4 AND reservation.res_date > '01-JAN-02'
5 AND reservation.res_date < '01-JAN-03';
Any pointers?Well, I doubt anyone will do all the work for you, but if all you are looking for is pointers...

Take a look at the BETWEEN command.

good luck!|||Originally posted by amesjustin
Well, I doubt anyone will do all the work for you, but if all you are looking for is pointers...

Take a look at the BETWEEN command.

good luck!

I wouldn't want anyone to the work. I have tried a between and couldn't figure how to get the statement to reflect between 30 and 60 days. How do you set up the statement to minus the thirty or sixty? I tried to To_char but my resources did not have good examples.|||I have tried a "between" but which variables would it be best to use it on or can you use two? We have had a real quick lesson on SQL. It is an Internet class so seeing the teacher is through emails. I put my statement of what I wrote at the very bottom. I was asking for pointers on my statement. I guess I get confused since there is two or three variables I am looking at. 1) all reservations in 2002 with 30 to 60 days reserved in advance, 2) setting it to be between Jan 1 and Dec 31 3) getting a number back on how many days it was too.
The "between" examples I saw usually followed the "where" and my "where" items are comparing an itinerary number and confirmation number. I like learning SQL but have noticed it is like java and any small detail can through it off like comma, period, paranthesis, and etc...

I have worked on this one problem over six hours. That is why I decided to enlist a discussion group for help. Thanks.

Monday, March 26, 2012

Hints

I am kind of confused about the way SQL Server 2000 handles the hints
that users supply with their SQL statements.

>From BOL, it seems that one can specify them with "WITH (...)" clauses
in SQL statements known as table hints. Sometimes, multiple uses of
this form in a statement is OK. Then there is the OPTION clause for
specifying statement hints. However, the documentation on OPTION
section discourages their use.

Being relatively new to SQL Server and still learning about it, what is
the general practice? Use hints or not? And if so, how (through WITH
or OPTION clauses)?

Cheers!<newtophp2000@.yahoo.com> wrote in message
news:1104122591.142300.19090@.f14g2000cwb.googlegro ups.com...
> I am kind of confused about the way SQL Server 2000 handles the hints
> that users supply with their SQL statements.
> >From BOL, it seems that one can specify them with "WITH (...)" clauses
> in SQL statements known as table hints. Sometimes, multiple uses of
> this form in a statement is OK. Then there is the OPTION clause for
> specifying statement hints. However, the documentation on OPTION
> section discourages their use.
> Being relatively new to SQL Server and still learning about it, what is
> the general practice? Use hints or not? And if so, how (through WITH
> or OPTION clauses)?

Generally, "don't".

Usually SQL server will make better guesses than you can.

> Cheers!|||>> what is the general practice? Use hints or not? <<

1) "Trust in the Optimizer, Luke!" It is usually smarter than you are.
What happens when you give a bad hint?

2) Once you write a query with a hint, that hint stays there. Even if
the pathological situation that made you use a hint heals up. Nobody
will dare remove it later, since it looks important.

3) Every product that supports hints has a different syntax and
underlying model, so your hint code will not port, and the logic of
your hint might not port either.

For example, in Sybase SQL Anywhere, you can give a guess as to what
percentage of the time a predicate will be TRUE. Their optimizer uses
that guess instead of it own computation to build the query. It is not
forced to use a particlar index or method. like other products.|||--CELKO-- wrote:

> 2) Once you write a query with a hint, that hint stays there. Even if
> the pathological situation that made you use a hint heals up. Nobody
> will dare remove it later, since it looks important.

That's funny! And too true!

Zach|||(newtophp2000@.yahoo.com) writes:
> I am kind of confused about the way SQL Server 2000 handles the hints
> that users supply with their SQL statements.
> From BOL, it seems that one can specify them with "WITH (...)" clauses
> in SQL statements known as table hints. Sometimes, multiple uses of
> this form in a statement is OK. Then there is the OPTION clause for
> specifying statement hints. However, the documentation on OPTION
> section discourages their use.
> Being relatively new to SQL Server and still learning about it, what is
> the general practice? Use hints or not? And if so, how (through WITH
> or OPTION clauses)?

Be very conservative with adding hints. In an ideal you would never have to
use them, but today I add two hints to one query: one index hint, and one
OPTION clause to turn of parallelism.

My general rule is that I add a hint, if 1) there is an apparent performance
problem and 2) there is an obvious choice of how the query plan should go.
The one hint I am the least conservative is OPTION (MAXDOP 1), because
even if the query would execute faster with parallelism, it will not at
least monopolize all processors in the machine. (And often parallel plans
are more ineffecient than the non-parallel plans.) The hint I am most
conservative of using is the join hint - you dump in a word between
INNER and JOIN, since this use of this hint results in a warning.

Whether to use WITH or OPTION depends on what you want to force. They
serve different purposes, therefore you cannot say that one is better
than the other.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> That's funny! And too true! <<

Remember the quote from early UNIX days? "There is nothing more
permanent than a temporary patch!"

Monday, March 19, 2012

High CPU value in Profiler

We see CPU values of 20000 + in Profiler for some statements ? Whats the
unit for it ? Is it in ms(milliseconds) ?
If its in milliseconds, what does that mean? Can a high duration of CPU
means higher processing power or just longer time to process ? Please help
me understand.Hassan, do you have Books Online? You should. From there:
"In SQL Server 2005, the server reports the duration of an event in
microseconds (one millionth, or 10^-6, of a second) and the amount of CPU
time used by the event in milliseconds (one thousandth, or 10^-3, of a
second). In SQL Server 2000, the server reported both duration and CPU time
in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user
interface displays the Duration column in milliseconds by default, but when
a trace is saved to either a file or a database table, the Duration column
value is written in microseconds."
A
"Hassan" <hassan@.hotmail.com> wrote in message
news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> unit for it ? Is it in ms(milliseconds) ?
> If its in milliseconds, what does that mean? Can a high duration of CPU
> means higher processing power or just longer time to process ? Please help
> me understand.
>|||Thanks Aaron.
What would 20000 ms mean from a CPU perspective ?
Is that considered a high CPU or is just running for 20 secs using some CPU
cycles ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hassan, do you have Books Online? You should. From there:
> "In SQL Server 2005, the server reports the duration of an event in
> microseconds (one millionth, or 10^-6, of a second) and the amount of CPU
> time used by the event in milliseconds (one thousandth, or 10^-3, of a
> second). In SQL Server 2000, the server reported both duration and CPU
> time in milliseconds. In SQL Server 2005, the SQL Server Profiler
> graphical user interface displays the Duration column in milliseconds by
> default, but when a trace is saved to either a file or a database table,
> the Duration column value is written in microseconds."
> A
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>> We see CPU values of 20000 + in Profiler for some statements ? Whats the
>> unit for it ? Is it in ms(milliseconds) ?
>> If its in milliseconds, what does that mean? Can a high duration of CPU
>> means higher processing power or just longer time to process ? Please
>> help me understand.
>|||Hassan
Run those statements and specify SET STATISTICS TIME ON
"Hassan" <hassan@.hotmail.com> wrote in message
news:eOjtAHAyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some
> CPU cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hassan, do you have Books Online? You should. From there:
>> "In SQL Server 2005, the server reports the duration of an event in
>> microseconds (one millionth, or 10^-6, of a second) and the amount of CPU
>> time used by the event in milliseconds (one thousandth, or 10^-3, of a
>> second). In SQL Server 2000, the server reported both duration and CPU
>> time in milliseconds. In SQL Server 2005, the SQL Server Profiler
>> graphical user interface displays the Duration column in milliseconds by
>> default, but when a trace is saved to either a file or a database table,
>> the Duration column value is written in microseconds."
>> A
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>> We see CPU values of 20000 + in Profiler for some statements ? Whats the
>> unit for it ? Is it in ms(milliseconds) ?
>> If its in milliseconds, what does that mean? Can a high duration of CPU
>> means higher processing power or just longer time to process ? Please
>> help me understand.
>>
>|||It means that 20 seconds worth of CPU cycles were used. So that could be
20 seconds of one CPU (or core), 10 seconds for 2 CPU's, etc. So if the
system only has one CPU (with one core), and the elapsed time was also
20 seconds, then this query has saturated the CPU for 20 seconds. If the
running time was 40 seconds (and still assuming 1 CPU), then the CPU was
used for (on average) 50% for this query.
HTH,
Gert-Jan
Hassan wrote:
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some CPU
> cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> > Hassan, do you have Books Online? You should. From there:
> >
> > "In SQL Server 2005, the server reports the duration of an event in
> > microseconds (one millionth, or 10^-6, of a second) and the amount of CPU
> > time used by the event in milliseconds (one thousandth, or 10^-3, of a
> > second). In SQL Server 2000, the server reported both duration and CPU
> > time in milliseconds. In SQL Server 2005, the SQL Server Profiler
> > graphical user interface displays the Duration column in milliseconds by
> > default, but when a trace is saved to either a file or a database table,
> > the Duration column value is written in microseconds."
> >
> > A
> >
> >
> > "Hassan" <hassan@.hotmail.com> wrote in message
> > news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> >> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> >> unit for it ? Is it in ms(milliseconds) ?
> >>
> >> If its in milliseconds, what does that mean? Can a high duration of CPU
> >> means higher processing power or just longer time to process ? Please
> >> help me understand.
> >>
> >
> >

Thursday, March 29, 2012

Homework assignment

Monday, March 26, 2012

Hints

Monday, March 19, 2012

High CPU value in Profiler

High CPU value in Profiler

High CPU value in Profiler

Hiding a Subreport

Blog Archive

About Me