kahunabee

This is beginner question. I am creating a resume website. I want to directly upload my resume (word document) so I do not have to retype it into <p></p>. I can do images and link to other websites, but I cannot find any information on this.

 

Thanks in advance.

 

KBee

I do not know how to do that.

 

I got some help and got this

 

with cte as (

select

case

when cast(Time_In as time) >'12:00:00' and cast(Time_In as time) <='15:00:00' and Visit_Date = cast(GETDATE() as date)then 'Session 2'

 

when cast(Time_In as time) >'3:00:00' and cast(Time_In as time)<= '6:00:00' and Visit_Date = cast(GETDATE() as date) then 'Session 3'

 

else 'Session 1'

 

end session

 

 

 

from Lab_Visits2

 

), ctecnt as (

 

select session, count(*) cnt

 

from cte

 

group by session

 

)

 

select session, (cnt)

 

from (

 

select session, cnt, row_number() over (order by cnt desc) rn

 

from ctecnt

 

) t

 

where rn = 1

I have a table that has the following fields:

 

Visit_ID

CPCCID

Date

Time_IN

Time-Out

Course

LA_CPCCID

 

there are 3 sessions, 9-12, 12-3 and 3-6

 

I need script that will calculate which session has the most visitors.

 

I have this attached code that will determine the session # and the max count

 

 

 

select Time_In ,

 

CASE

 

 

When cast(Time_In as time) >'12:00:00' and cast(Time_In as time) <='15:00:00' /* and date = cast(GETDATE() as date)*/ then 'Session 2'

 

when cast(Time_In as time) >'3:00:00' and cast(Time_In as time)<= '6:00:00' /*and date = cast(GETDATE() as date)*/ then 'Session 3'

 

else 'Session 1'

 

end "sessions"

 

from Lab_Visits2;

 

 

 

 

 

select max(visit.cnt)

 

 

from

 

 

(select course, count(course) cnt

 

 

from Lab_Visits2

 

 

group by Course) visit;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

that might work

 

thanks

I want to

 

fire a trigger that will fire after the update of the productid on table a.

 

The trigger will need to update multiple rows on 2 different tables.

 

Is this possible?

 

 

I am just learning sql - taking classes at community college. I have an assignment that I have to print a commission rate (with following %) from a basic select statement.

 

I tried doing this with a to_char(value, '99%') and received an error (I do know % is used as sql keywords)

 

ORA-01481:invalid number format modelCause: The user is attempting to either convert a number to a string via TO_CHAR or a string to a number via TO_NUMBER and has supplied an invalid number format model parameter. Action: Consult your manual.

I am trying to write a PL/SQL program that will call a procedure. The user will enter a cutoff date in the calling program and pass it to the procedure where it is being used to check in the bb_basket table to pull shoppers who have not been to the website since before the cutoff date.

The shoppers that the select retrieves will be inserted into the promolist table.

 

I am getting the ora-01422 error exact fetch returns more than requested rows. Code is below, any help would be appreciated.

 

 

This is the code for my PL/SQL block that is calling my procedure:

 

 

set serveroutput on;

set verify off;

declare

t_cutoffdate date := '&v_cutoffdate';

v_yearcheck number(4,0) := to_number(to_char(t_cutoffdate, 'yyyy'));

v_mon char(3) := (to_char(t_cutoffdate, 'mon'));

v_idshopper bb_promolist.idshopper%type;

v_month bb_promolist.month%type;

v_year bb_promolist.year%type;

v_promoflag bb_promolist.promo_flag%type;

v_used bb_promolist.used%type;

begin

if v_yearcheck < 2011 or v_yearcheck > 2013 then

dbms_output.put_line ('Please enter a year between 2011 and 2013');

else

promo_ship_sp(t_cutoffdate);

end if;

select idshopper,month,year,promo_flag,used

into v_idshopper,v_month,v_year,v_promoflag,v_used

from bb_promolist;

dbms_output.put_line (v_idshopper);

end;

/

 

this is my procedure (promo_ship_sp)

 

create or replace

procedure promo_ship_sp

(p_date in date)

is

cursor cur_promo is

select idshopper, max(dtcreated) createdate

from bb_basket

group by idshopper

having max(dtcreated) < p_date;

type t_rec is record

(shopperid bb_basket.idshopper%type,

createdate bb_basket.dtcreated%type);

rec_promo t_rec;

v_month char(3) := 'APR';

v_year number(4,0) := 2012;

v_promoflag number(1,0) := 1;

v_used char(1) := 'N';

begin

open cur_promo;

loop

fetch cur_promo into rec_promo;

exit when cur_promo%notfound;

insert into bb_promolist

values (rec_promo.shopperid, v_month,v_year,v_promoflag,v_used);

end loop;

close cur_promo;

END PROMO_SHIP_SP;

my assignment is below with the code following it. I am getting error code 00103 encountered "group" when expecting (a long list of commands followed, such as begin, case, declare, end...) Any suggestions????????

• [*][*]. Write a PL/SQL block that determines whether a customer is rated high, med, or low based on his/her total purchase. The block needs to determine the rating and then display the results on screen. The program rates the customer high if total purchases are greater than $200, mid if greater than $100, and low if $100 or lower. You must use IF structure in your program. You must accept the customer ID (shopper ID) from the user and display a message that includes the customer’s number, firstname, lastname, and the rating. See sample output below.

anonymous block completedCustomer 23 Kenny RatmanThe total amount is 81.75 and the rating is LOW set serveroutput on;

declare

v_idshopper bb_basket.idshopper%type := &idshopper; v_rating char(5); v_totalbasket bb_basket.total%type; v_firstname bb_shopper.firstname%type; v_lastname bb_shopper.lastname%type;begin select firstname, lastname, sum(total) into v_firstname, v_lastname, v_totalbasket from bb_shopper join bb_basket using (idshopper) where v_idshopper like idshopper; group by firstname, lastname;

if v_totalbasket > 200 then v_rating := 'high'; elseif v_totalbasket > 100 then v_rating := 'mid'; else v_rating := 'low'; end if;

dbms_output.put_line ('Customer ' ||v_idshopper ||' ' ||v_firstname ||' ' ||v_lastname;dbms_output.put_line ('The total amount is '||v_totalbasket || (' and the rating is '||' v_rating);end;/

I am just learning SQL and my assignment has the user entering their birthdate and the block will print their age and let them know if they were born in a leap year. Criteria for leap year is the year is divisible by 4 and 400 but not 100.

 

Here is my script below. It works fine if the year is not divisible by 100, I tested 1964 and 1989. But it shows years 1900 and 2000 as leap years.

If anyone can help, I would greatly appreciate it.

 

--2set serveroutput on;set verify on;DECLARE v_birthdate DATE; v_age NUMBER; V_LEAP NUMBER; begin-- get birthdate

v_birthdate := '&v_birthdate';dbms_output.put_line ('your birthdate is ' || v_birthdate);

v_age := round(months_between(sysdate,v_birthdate)/12,1);dbms_output.put_line ('your age is ' ||v_age);if mod(substr(v_birthdate,8,4),100) = 0then if mod(substr(v_birthdate,8,4),400) = 0 then DBMS_OUTPUT.PUT_LINE ('your birthyear is a leap year'); else DBMS_OUTPUT.PUT_LINE ('your birthyear is not a leap year'); end if;else if mod(substr(v_birthdate,8,4),4) = 0 then dbms_output.put_line ('your birthyear is a leap year'); else dbms_output.put_line ('your birthyear is not a leap year'); end if; end if;end;/

Here is my assignment that I need help with:

 

Create a view that includes details for all crimes, including criminal ID, criminal name, criminal parole status, crime ID, date of crime charge, crime status, charge ID, crime code, charge status, pay due date, and amount due. This view should not store view data and should not allow performing any DML operations.